Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-03-13 : 08:56:30
|
Norm Johanson writes "I need to determine how many customer orders I can ship this month, based on my inventory on hand quantity. I also need to know which orders I can ship, and which orders I cannot. A simple way is to use a "running total", where the Available from one line becomes the Inventory Amount for the next, similiar to a checkbook.If I create a simple (temporary) table that shows:SKU DueDate Inventory OnOrder Available ABC 03-21-02 40 20 20 ABC 03-22-02 20 30 -10 ABC 03-23-02 -10 40 -50 XYZ 03-21-02 50 35 15XYZ 03-22-02 15 20 -5XYZ 03-23-02 -5 30 -35 I have tried using a running total (mentioned in another of your articles) and it will work great for the first set of SKUs. However, I need to reset the running total for the next set of SKUs, if I am going to use this method.Any help would be greatly appreciated.TIA.Norm Johanson" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-13 : 09:17:45
|
Garth's running total article:http://www.sqlteam.com/item.asp?ItemID=3856has a cursor solution that you could modify to reset the total as the SKU changes from one group to the next. You'll need to declare a @SKU and @SKU_Previous variable, for example, and compare the two after the FETCH:FETCH NEXT FROM rt_cursor INTO @SKU, @availableSET @SKU_Previous=@SKUWHILE @@FETCH_STATUS = 0 BEGIN SET @RunningTotal = CASE @SKU WHEN @SKU_Previous THEN @RunningTotal + @available ELSE @available END[/b] INSERT #Sales VALUES (@SKU, @available, @RunningTotal) SET @SKU_Previous=@SKU[/b] FETCH NEXT FROM rt_cursor INTO @SKU, @available END |
 |
|
|
|
|
|
|
|
Running Total, I think.