StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I think this problem can be solved using purely set-based approach.</p> <p>Basically, you need to perform these steps:</p> <ol> <li><p>Obtain the table of currently available quantity for every item.</p></li> <li><p>Obtain the running totals from the ordered quantity due to be processed.</p></li> <li><p>Get <code>QtyAvailableAfterAllocation</code> for every item as the result of subtraction of its running total from its available quantity.</p></li> </ol> <p>Here's a sample solution:</p> <pre><code>/* sample data definition & initialisation */ DECLARE @LastQty TABLE (Item int, Qty int); INSERT INTO @LastQty (Item, Qty) SELECT 0123, 404 UNION ALL SELECT 1234, 505 UNION ALL SELECT 2345, 606 UNION ALL SELECT 3456, 707 UNION ALL SELECT 4567, 808 UNION ALL SELECT 5678, 909; DECLARE @Orders TABLE (ID int, Item int, OrderedQty int); INSERT INTO @Orders (ID, Item, OrderedQty) SELECT 1, 1234, 5 UNION ALL SELECT 2, 1234, 15 UNION ALL SELECT 3, 2345, 3 UNION ALL SELECT 4, 1234, 10 UNION ALL SELECT 5, 2345, 37 UNION ALL SELECT 6, 2345, 45 UNION ALL SELECT 7, 3456, 50 UNION ALL SELECT 8, 4567, 25 UNION ALL SELECT 9, 2345, 30; /* the actuall query begins here */ WITH RankedOrders AS ( SELECT *, rn = ROW_NUMBER() OVER (PARTITION BY Item ORDER BY ID) FROM @Orders ), RunningOrderTotals AS ( SELECT ID, Item, OrderedQty, RunningTotalQty = OrderedQty, rn FROM RankedOrders WHERE rn = 1 UNION ALL SELECT o.ID, o.Item, o.OrderedQty, RunningTotalQty = r.RunningTotalQty + o.OrderedQty, o.rn FROM RankedOrders o INNER JOIN RunningOrderTotals r ON o.Item = r.Item AND o.rn = r.rn + 1 ) SELECT t.ID, t.Item, t.OrderedQty, QtyAvailableAfterAllocation = oh.Qty - t.RunningTotalQty FROM RunningOrderTotals t INNER JOIN @LastQty oh ON t.Item = oh.Item ORDER BY t.ID; </code></pre> <p>Note: For the purpose of my example I initialised the available item quantity table (<code>@LastQty</code>) manually. However, you are most probably going to derive it from your data.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload