SUM OVER PARTITION or LATERAL

[GlennGG]

Hi there,

I'm not sure what's the best method to obtain a running total but any working method would be great. I've seen many examples but none for exactly what I need.
I have a table that has the first 3 columns with data. I'd like to calculate the 4th column, which is a running total of order qty for the part, regardless of the order #.
I need to retain the arrival sequence of the records (or sorted by order #, part #). My attempts at this seem to reorder the data by part # or I get a total order qty and not running total.

Order #	Part #	Order Qty	Running Total of Order Qty for Part
11111	AAAAA	1	1
11111	BBBBB	2	2
11111	CCCCC	3	3
22222	BBBBB	4	6
22222	CCCCC	5	8
33333	AAAAA	6	7

Thank you.

Glenn Gundermann

[GlennGG]

I think I figured it out:

with t as (
select order_no, part_no, order_qty,
sum(order_qty) over(partition by part_no order by order_no) as running_total_part_order_qty
from orders)
select * from t order by order_no, part_no;

[GlennGG]

The requirements got trickier and I'm wondering if anyone knows how to do this:
Given the following, assume all of these order lines are for the same part. The Qty Available is from the part master.
The sum over partition statement above will produce the Running Total you see below.

Order Qty	Valid	Running Total	Qty Available	Result
500	Y	500	1100	Ship
500	Y	1000	1100	Ship
500	Y	1500	1100	Cancel
1	N	1501	1100	Cancel
1	Y	1502	1100	Ship

The third line doesn't have enough qty available so will not ship. I'd like the running total to remain at 1000.
The fourth line is not valid so I'd like the running total to remain at 1000.
The fifth line has enough qty available so will ship. I'd like the running total to show 1001.
I'm basically looking for the running total to only include lines if it doesn't exceed the qty available and meet the condition valid = Y.
Is this doable using sum over partition, lateral, sub-query, or what?

[MFisher]

I don't know, but if your question is strictly SQL based you might try this forum.

https://www.sqlservercentral.com/forums

Keep in mind syntax and options are likely to be a bit different between IBM SQL and MSSQL