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?

CodePudding user response：

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.