set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/*
EXEC USP_RESTAA0
 @id     = '1',
 @input_charge  = '2',
 @output_charge  = '3',
 @in_out_gap   = '4'
*/

ALTER PROCEDURE [dbo].[USP_RESTAA0]
(  
@id   VARCHAR(10)  = NULL,
@input_charge  VARCHAR(10)  = NULL,
@output_charge  VARCHAR(10)  = NULL,
@in_out_gap  VARCHAR(10)  = NULL
) AS

DECLARE  @B_id  INT      -- B_table 쉬컨스 갯수
SET  @B_id = 0

CREATE TABLE #B_TEMP
 ( 
  flag NUMERIC(2,0) DEFAULT(0),
  id  NUMERIC(18, 0) DEFAULT(0),
  put_id VARCHAR(10) DEFAULT NULL
 )

CREATE TABLE #A_TEMP
 ( 
  flag NUMERIC(2,0) DEFAULT(0),
  id  VARCHAR(10) DEFAULT NULL,
  charge  NUMERIC(10) DEFAULT(0)
 )

CREATE TABLE #TEMP
 ( 
  id  VARCHAR(10) DEFAULT NULL,
  input_charge  NUMERIC(18, 0) DEFAULT(0) ,
  output_charge  NUMERIC(18, 0) DEFAULT(0),
  in_out_gap  NUMERIC(18, 0) DEFAULT(0) ,
 )

CREATE TABLE #TOTAL_TEMP
 ( 
  id  VARCHAR(10) DEFAULT NULL,
  input_charge  NUMERIC(18, 0) DEFAULT(0) ,
  output_charge  NUMERIC(18, 0) DEFAULT(0),
  in_out_gap  NUMERIC(18, 0) DEFAULT(0) ,
 )

begin
 --select * From SET_B  5는 최종갯수
 WHILE ( @B_id <= 5)
 BEGIN
  SET @B_id = @B_id +1  -- INPUT_ID
   INSERT INTO #B_TEMP select 0 As Flag, B.id As id , B.INPUT_ID As put_id From SET_B As B Where B.id = @B_id
 END

 SET @B_id = 0

 WHILE ( @B_id <= 5)
 BEGIN
  SET @B_id = @B_id +1 -- ACCETP_ID
   INSERT INTO #B_TEMP select 1 As Flag, B.id As id , B.ACCETP_ID As put_id From SET_B As B Where B.id = @B_id
 END

end

begin
 INSERT INTO #A_TEMP select  flag, put_Id, charge As charge from #B_TEMP AS B INNER JOIN SET_A As A ON A.id = B.id group by B.flag , B.put_ID, charge
end

INSERT INTO #TEMP
  select A.id,
   (CASE WHEN Flag=0 then A.Charge else 0 end) As input_charge,   
   (CASE WHEN Flag=1 then A.Charge else 0 end) As output_charge,
  0 as in_out_gap 

     from #A_TEMP As A group by A.flag, A.Id, A.Charge

INSERT INTO #TOTAL_TEMP
  select id,
      SUM(input_charge) As input_charge,
               SUM(output_charge) As output_charge,
      0 as in_out_gap 
  from #TEMP group by id

select id, input_charge, output_charge,
  input_charge - output_charge As in_out_gap
from #TOTAL_TEMP AS T

DROP TABLE #B_TEMP
DROP TABLE #A_TEMP
DROP TABLE #TEMP
DROP TABLE #TOTAL_TEMP