I am trying to build a query with a running total, but it is a bit
complicated.
We have different stock for leaded and unleaded parts, but we also have
one stock if it is lead free and can be used in a leaded process (lfb -
lead free both).
I have the following fields: id,fkey_id,partid, duedate, qty, leadfree,
stock_l, stock_lf, stock_lfb
I want to write a query that tells me for each row how much leaded stock
I have already committed and how much lead free stock I have committed.
If we use stock that is LFB then it goes on the commitment for both
columns. If it is a lead free row and we do not have enough stock, it is
only a commitment for the lead free stock (same idea for the leaded rows).
The rule is that if you have leaded or lead free stock, you use that
before LFB stock.
The table can be self joined on partid where the second table has an
earlier duedate (and an earlier fkey_id if the dates are the same)
I have rows:
id fkey_id partid duedate qty leadfree stock_l
stock_lf stock_lfb
1 100 126 1/1/2011 100 t 10
15 300
2 105 126 1/2/2011 150 t 10
15 300
3 109 126 1/3/2011 200 f 10
15 300
4 110 126 1/4/2011 100 t 10
20 300
5 115 126 1/5/2011 200 f 10
20 300
6 114 126 1/6/2011 500 t 10
20 300
My end result should look like:
id fkey_id partid duedate qty leadfree stock_l
stock_lf stock_lfb commited_L commited_LF
1 100 126 1/1/2011 100 t 10
15 300 0 0
2 105 126 1/2/2011 150 t 10
15 300 85 100
3 109 126 1/3/2011 200 f 10
15 300 235 250
4 110 126 1/4/2011 100 t 10
20 300 435 315
5 115 126 1/5/2011 200 f 10
20 300 435 415
6 114 126 1/6/2011 500 t 10
20 300 635 415
Currently I'm generating this information using a plpsql loop, but I
would like to change it to an SQL query, if possible.
Thanks
Sim