Need help in identifying potential item shortages

[JeffMachine]

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 ....

 

[SQLBill]

Isn't -1 a negative value? Wouldn't you want to know when you have one item less than what you need?

Or do you only care when it is -2 or more?

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[JeffMachine]

Qty needs to be accumulated, once the Accumulated Qty hits a minus number, thats what I need to know. The records are ordered by reqdate, when the accumulated qty goes negative I need the reqdate of the item that goes negative, this tells me the date the item is shceduled to go negative. I also need the reqdate of the next (after the acculuated negative) positive item. It doesn't' have to cover the full negative amount.

Basically, the report would tell me when an item is scheduled to go negative and when the next replenishment is scheduled after that happens.

Thanks ....