Rolling On-Hand Remainder column?

Question

CONumber, LineNumber, PartNumber, OrderQty, ScheduleDate, OnHandQty columns are a pure SELECT query with no transformations. I am trying to recreate the RollingOnHand column in SQL.

The rules are

1. If a part only has one row, report the real [OnHandQty]
2. If a part has multiple rows, the oldest order consumes its [OrderQty] from [OnHandQty]
3. The next oldest order pulls its [OrderQty] from the remaining [OnHandQty], repeat until final row of the matching part
4. The last row of a given part will display the remaining [OnHandQty]

Is this possible to accomplish in an SQL query?

CONumber	LineNumber	PartNumber	OrderQty	ScheduleDate	OnHandQty	RollingOnHand
C02959	00002	Part 01	102	2022-04-01	0	0
C04017	00001	Part 02	2007	2022-04-01	5099	5099
C04107	00001	Part 03	1	2022-03-09	0	0
C04106	00001	Part 04	1	2022-03-09	0	0
C04108	00001	Part 05	1	2022-03-09	0	0
C03514	00002	Part 06	250	2022-03-11	310	250
C03514	00003	Part 06	250	2022-03-18	310	60
C03757	00001	Part 06	250	2022-04-06	310	0
C04225	00002	Part 07	40	2022-03-31	53	53
C03965	00002	Part 08	24	2022-04-04	0	0
C04034	00001	Part 09	88	2022-03-18	128	128
C04144	00002	Part 10	22	2022-04-04	0	0
C04141	00001	Part 10	100	2022-04-04	0	0
C03734	00003	Part 11	116	2022-03-29	103	103
C03379	00001	Part 12	128	2022-03-07	19	19
C03344	00003	Part 13	40	2022-03-11	5	5
C04058	00001	Part 14	407	2022-03-25	0	0
C03697	00002	Part 15	436	2022-04-04	235	235
C03689	00002	Part 16	111	2022-03-16	87	87
C03690	00001	Part 16	250	2022-03-23	87	0
C03690	00002	Part 16	250	2022-04-06	87	0
C03240	00004	Part 17	3	2022-03-16	30	3
C03725	00001	Part 17	250	2022-03-16	30	27
C03725	00002	Part 17	250	2022-03-23	30	0
C03726	00001	Part 17	250	2022-04-01	30	0
C03726	00002	Part 17	250	2022-04-06	30	0
C03596	00017	Part 18	56	2022-04-06	344	344
C03927	00001	Part 19	600	2022-04-04	1800	600
C03927	00002	Part 19	1000	2022-04-06	1800	1200

Answer 1

I think this basically does what you need (Fiddle)

WITH T AS
(
SELECT *, 
        AlreadyConsumed = SUM(OrderQty) OVER (PARTITION BY [PartNumber] ORDER BY  ScheduleDate ASC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),
        PrevLineNumber = LAG([LineNumber]) OVER (PARTITION BY [PartNumber] ORDER BY  ScheduleDate ASC),
        NextLineNumber = LEAD([LineNumber]) OVER (PARTITION BY [PartNumber] ORDER BY  ScheduleDate ASC)
FROM Demo
)
SELECT CONumber,
       LineNumber,
       PartNumber,
       OrderQty,
       ScheduleDate,
       OnHandQty,
       RollingOnHand = CASE
                         --If a part only has one row, report the real [OnHandQty]
                         WHEN PrevLineNumber IS NULL
                              AND NextLineNumber IS NULL THEN OnHandQty
                         --Not the last row and won't use all the remainder up
                         WHEN NextLineNumber IS NOT NULL AND Remainder > OrderQty THEN OrderQty
                         --otherwise use what's left
                         ELSE Remainder
                       END
FROM   T
CROSS APPLY (SELECT CASE WHEN AlreadyConsumed > OnHandQty THEN 0 ELSE OnHandQty - ISNULL(AlreadyConsumed,0) END) C(Remainder)

The

SUM ... PARTITION BY [PartNumber] ... ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING computes the cumulative OrderQty for all rows before the current row (not including it)

The LAG/ LEAD results are used as indicators to determine whether we are in the first/last rows of a partition and special logic is needed.

I didn't quite follow the rationale behind the business logic so I may have made some invalid simplifications but it returns the desired results with the sample data and anyway the query should be easy to tweak if needed.