Running total of row data until goal (from another table) is met

Transact-SQL
1

Create tables and load data:

create table #testData (
	ID varchar(15),
	QTY_NEEDED decimal(20,8),
	NEED_DATE datetime)

create table #onHand (
	ID varchar(15),
	QTY_ON_HAND decimal(20,8) )


insert into #testData
	(ID, QTY_NEEDED, NEED_DATE)
values
	(100, 325, '2023-04-01'),
	(100, 500, '2023-05-01'),
	(100, 200, '2023-06-01'),
	(101, 405, '2023-04-01'),
	(101, 1592, '2023-05-01'),
	(101, 750, '2023-06-01'),
	(101, 550, '2023-07-01'),
	(102, 550, '2023-05-01'),
	(102, 550, '2023-06-01')

insert into #onHand
	(ID, QTY_ON_HAND)
values
	(100, 900),
	(101, 2750),
	(102, 0)

I need to find the NEED_DATE at which QTY_ON_HAND goes negative after deducting #testData.QTY_NEEDED from #onHand.QTY_ON_HAND.

Thus, for ID 100, I need to return 2023-06-01, for ID 101 I am looking for 2023-07-01, and for ID 102 I expect to see 2023-05-01

2 
WITH Totals
AS
(
	SELECT Id, Need_Date
		,SUM(Qty_Needed) OVER (PARTITION BY Id ORDER BY Need_Date) AS Total_Needed
	FROM #testData
)
SELECT T.Id, MIN(T.Need_Date) AS Neg_Date
FROM Totals T
	JOIN #onHand H
		ON T.Id = H.Id
WHERE T.Total_Needed > H.QTY_ON_HAND
GROUP BY T.Id;
1 Like
3

That's perfect - thanks!