Pfthb

Table 1:

Table 2:

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

Thanks!

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;

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

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