I have the following, and need to be convert and insert into a new table. This could be done in a some sort of reporting tool, but for now this what I need.
Server: SQL 2000
------------------
table1
table2 - Need something like this: Base on the Date of Inventory A(Acquire), and S(Ship). Eg: Account 101 had 500 on 10/1/2008,
but only 200 was ship on 11/1/2008 which result have 300. left. The 300 will be use to substract from the next ship date.
I been struggle with this all morning, and wonder if anyone have a pointer get this started.
thanks
Server: SQL 2000
------------------
table1
Code:
Acct Date Tran Qty
101 10/1/2008 A 500
101 10/2/2008 A 1000
101 10/3/2008 A 200
102 10/5/2008 A 500
102 10/6/2008 A 500
101 11/1/2008 S 200
101 11/2/2008 S 1000
101 11/3/2008 S 200
101 11/4/2008 S 400
102 11/5/2008 S 700
table2 - Need something like this: Base on the Date of Inventory A(Acquire), and S(Ship). Eg: Account 101 had 500 on 10/1/2008,
but only 200 was ship on 11/1/2008 which result have 300. left. The 300 will be use to substract from the next ship date.
Code:
Acct bDate bTran bQty sDate sTran sQty
101 10/1/2008 A 200 11/1/2008 S 200
101 10/1/2008 A 300 11/2/2008 S 300
101 10/2/2008 A 700 11/2/2008 S 700
101 10/2/2008 A 200 11/3/2008 S 200
101 10/2/2008 A 100 11/4/2008 S 100
101 10/3/2008 A 200 11/4/2008 S 200
101 * * * 11/4/2008 S 100
102 10/5/2008 A 500 11/5/2008 S 500
102 10/6/2008 A 200 11/5/2008 S 200
102 10/6/2008 A 300 * * *
I been struggle with this all morning, and wonder if anyone have a pointer get this started.
thanks