April 19, 2018 at 8:43 am
Hello,
i may be over thinking this, but i have a situation where we have a requirement for a part, example called "2x4", current on hand is 10, there are 6 orders each order requires 2 "2x4"... so i am looking for the report to look:
order number Item order qty on hand left
1 2x4 2 10 8
2 2x4 2 8 6
3 2x4 2 6 4
4 2x4 2 4 2
5 2x4 2 2 0
6 2x4 2 0 -2
how can i accomplish this?
April 19, 2018 at 9:03 am
Something like this:SELECT
OrderNo
, Item
, OrderQty
, x.OnHand - SUM(OrderQty) OVER (PARTITION BY Item ORDER BY OrderNo ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS OnHand
, x.QtyLeft - SUM(OrderQty) OVER (PARTITION BY Item ORDER BY OrderNo ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS QtyLeft
FROM MyTable t
CROSS APPLY (
SELECT
OnHand
, OnHand - OrderQty AS QtyLeft
FROM MyTable
WHERE Item = t.Item
) x;
Please post table DDL in the form of a CREATE TABLE statement and sample data in the form of INSERT statements if you prefer a tested solution free of syntax errors.
John
April 21, 2018 at 9:36 pm
Sorry John, but big thanks for answering the question, it worked perfectly 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply