CODEWITHSUNDEEP
sqlsql-serverleft-join
Sarah
Sarah

Reputation: 1303

SQL Server query with logic, outer apply

I need help with the following query. I am trying to subtract the Deduct amount per acc_No from the amount column. The running amount column should reduce with every amount for that accountNo. After all subtractions are done and if there is any amount left, that should go on the reportAmount column. Report column I need help with and some of the entries in the Running amount column are not matching.

DECLARE @table AS TABLE
                  (
                      acc_no INT, 
                      [group] CHAR(1), 
                      DeductAmount FLOAT, 
                      amount FLOAT
                  )

INSERT INTO @table 
VALUES (101, 'A', -100, 0), (101, 'B', -100, 20),
       (101, 'C', -100, 70), (101, 'D', -100, 20),
       (102, 'A', -20, 30), (102, 'B', -20, 0),
       (102, 'C', -20, 5), (102, 'D', -20, 10),
       (103, 'A', -80, 40), (103, 'B', -80,15),
       (103, 'C', -80, 10), (103, 'D', -80, 5),
       (104, 'A', -30, 0), (104, 'B', -30,85),
       (104, 'C', -30, 5), (104, 'D', -30, 10),
       (105, 'A', -15, 7), (105, 'B', -15,25),
       (105, 'C', -15, 5), (105, 'D', -15, 10)

-- SELECT * FROM @table

SELECT 
    t.acc_no, 
    t.[group],
    t.DeductAmount,amount,--t1.LeftFromDeduct, 
    CASE 
       WHEN (t.DeductAmount + t1.LeftFromDeduct) >= 0 
          THEN 0 
          ELSE (t.DeductAmount + t1.LeftFromDeduct) 
    END RunningAmount
FROM
    @table t
OUTER APPLY
    (SELECT  
         SUM([amount]) AS LeftFromDeduct 
     FROM 
         @table t1 
     WHERE 
         t1.acc_no = t.acc_no AND t1.[group] <= t.[group]) t1

When I execute it I get the following results:

acc_no  group   DeductAmount    amount  RunningAmount
-----------------------------------------------------
101 A   -100    0   -100
101 B   -100    20  -80
101 C   -100    70  -10
101 D   -100    20  0
102 A   -20 30  0
102 B   -20 0   0
102 C   -20 5   0
102 D   -20 10  0
103 A   -80 40  -40
103 B   -80 15  -25
103 C   -80 10  -15
103 D   -80 5   -10
104 A   -30 0   -30
104 B   -30 85  0
104 C   -30 5   0
104 D   -30 10  0
105 A   -15 7   -8
105 B   -15 25  0
105 C   -15 5   0
105 D   -15 10  0

My expected result should look something like this, I need help in getting the logic corrected to find the runningAmount and ReportAmount columns.

acc_no  group   DeductAmount    amount  RunningAmount   ReportAmount
101 A   -100    0   -100    0
101 B   -100    20  -80 0
101 C   -100    70  -10 0
101 D   -100    20  10  10
102 A   -20 30  0   10
102 B   -20 0   0   0
102 C   -20 5   0   5
102 D   -20 10  0   10
103 A   -80 40  -40 0
103 B   -80 15  -25 0
103 C   -80 10  -15 0
103 D   -80 5   -10 0
104 A   -30 0   -30 0
104 B   -30 85  0   55
104 C   -30 5   0   5
104 D   -30 10  0   10
105 A   -15 7   -8  0
105 B   -15 25  -7  18
105 C   -15 5   0   5
105 D   -15 10  0   10

Upvotes: 0

Views: 44

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Based on your description, you seem to want:

select t.*,
       (case when runningamount_tmp > 0 then 0 else runningamount_tmp end) as running_amount,
       (case when runningamount_tmp > 0 then runningamount_tmp else 0 end) as reportamount
from (select t.*,
             (deductamount + sum(amount) over (partition by acc_no order by group)) as runningamount_tmp     
      from @table t
     ) t;

Upvotes: 1

Related Questions