CODEWITHSUNDEEP
sqlsql-servert-sql
MrB
MrB

Reputation: 101

Cumulative subtraction across rows

Table 1:
table1:

Table 2:
enter image description here

How can I subtract the Committed value (7) from the IncomingQuantity cumulatively across rows? So that the result would look like:
enter image description here

Thanks!

Upvotes: 0

Views: 881

Answers (3)

MrB
MrB

Reputation: 101

I ended up doing this simply with WHILE loop inside a user function. The other solutions I would not work properly in 100% of cases

Upvotes: 0

iSR5
iSR5

Reputation: 3498

you can also make use of the built-in functions such as ROW_NUMBER(), LAST_VALUE(), and LAG() with CASE

here is an example : 

DECLARE 
    @t1 TABLE ( ProductID VARCHAR(50), ICommitted INT)

INSERT INTO @t1 VALUES ('Some product', 7)

DECLARE 
    @t2 TABLE (RowID INT, DueDate DATE, IncommingQuantity INT)

INSERT INTO @t2 VALUES 
(1,'2018-11-19', 5),
(2,'2018-11-20', 4),
(3,'2018-11-20', 4),
(4,'2018-11-20', 3),
(5,'2018-11-22', 12)

SELECT  
    RowID 
,   DueDate
,   CASE 
        WHEN RowID = 1 
        THEN 0 
        WHEN RowID = LAST_VALUE(RowID) OVER(ORDER BY (SELECT NULL) )    
        THEN IncommingQuantity
        WHEN ROW_NUMBER() OVER(PARTITION BY DueDate ORDER BY RowID) > 1 
        THEN IncommingQuantity
        ELSE ICommitted - LAG(IncommingQuantity) OVER (ORDER BY RowID)
    END  IncommingQuantity
FROM @t2 t2
CROSS APPLY (SELECT t1.ICommitted FROM @t1 t1) e

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You need a cumulative sum and some arithmetic:

select t.*,
       (case when running_iq - incomingquantity >= committed then 0
             when running_iq > committed then running_iq - committed
             else incomingquantity
        end) as from_this_row
from (select t2.*, t1.committed,
             sum(incomingquantity) over (order by rowid) as running_iq
      from table1 t1 cross join
           table2 t2
     ) t;

Upvotes: 2

Related Questions