Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need help in identifying potential item shortages

Status
Not open for further replies.
May 17, 2006
54
US
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 ....
 
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
 
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 ....
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top