JeffMachine
MIS
I have data from a manufacturing system and each row represents a transaction. I need to know when an item is scheduled to go negative and the next scheduled replenishment date and amount after that.
Sample data. Data is not stored in order, will need top be sorted by itemid,reqdate.
ItemId[/indent]ReqDate[/indent]Qty
Item1[/indent]1/20/15[/indent]1
Item1[/indent]1/21/15[/indent]2
Item1[/indent]1/22/15[/indent]-1
Item1[/indent]1/24/15[/indent]-1
Item1[/indent]1/25/15[/indent]-3
Item1[/indent]1/26/15[/indent]5
Item2[/indent]1/20/15[/indent]1
Item2[/indent]1/22/15[/indent]-1
Item2[/indent]1/29/15[/indent]-3
Item2[/indent]1/30/15[/indent]1
Item2[/indent]1/31/15[/indent]4
Item3[/indent]1/20/15[/indent]5
Item3[/indent]1/22/15[/indent]-1
Item3[/indent]1/29/15[/indent]-1
Item3[/indent]1/30/15[/indent]-1
I need a query that returns to me:
[ul]
[li]Itemid[/li]
[li]accumulated QTY the first instance the accumulated Qty goes negative[/li]
[li]that record's Reqdate[/li]
[li]the reqdate and Qty of the first positive Qty (replenishment) record after the first instance the accumulated Qty goes negative[/li]
[li][/li]
[/ul]
Results would be:
Item1[/indent]-2[/indent]1/25/15[/indent]5[/indent]1/26/15
Item2[/indent]-3[/indent]1/29/15[/indent]1[/indent]1/30/15
(Item3 never goes negative so no return.)
Thanks ....
Sample data. Data is not stored in order, will need top be sorted by itemid,reqdate.
ItemId[/indent]ReqDate[/indent]Qty
Item1[/indent]1/20/15[/indent]1
Item1[/indent]1/21/15[/indent]2
Item1[/indent]1/22/15[/indent]-1
Item1[/indent]1/24/15[/indent]-1
Item1[/indent]1/25/15[/indent]-3
Item1[/indent]1/26/15[/indent]5
Item2[/indent]1/20/15[/indent]1
Item2[/indent]1/22/15[/indent]-1
Item2[/indent]1/29/15[/indent]-3
Item2[/indent]1/30/15[/indent]1
Item2[/indent]1/31/15[/indent]4
Item3[/indent]1/20/15[/indent]5
Item3[/indent]1/22/15[/indent]-1
Item3[/indent]1/29/15[/indent]-1
Item3[/indent]1/30/15[/indent]-1
I need a query that returns to me:
[ul]
[li]Itemid[/li]
[li]accumulated QTY the first instance the accumulated Qty goes negative[/li]
[li]that record's Reqdate[/li]
[li]the reqdate and Qty of the first positive Qty (replenishment) record after the first instance the accumulated Qty goes negative[/li]
[li][/li]
[/ul]
Results would be:
Item1[/indent]-2[/indent]1/25/15[/indent]5[/indent]1/26/15
Item2[/indent]-3[/indent]1/29/15[/indent]1[/indent]1/30/15
(Item3 never goes negative so no return.)
Thanks ....