March 7, 2014 at 10:09 am
I'm trying to create a query that will roll through sales orders by item in date (PromiseDate)order. With a known inventory amount (QtyOnHand) that subtracts the QtyOrdered amount for each order and keeps a running total of the QtyOnHand after subtracting the QtyOrdered and only writes to my table when the QtyOnHand is < 1 that way I know the date e specific item will be out of inventory.
After processing the follow data my new table should contain three records, one for each item representing the date that item would run out of inventory.
Item PromiseDate QtyOrdered QtyOnHand
101002014-03-03 00:00:00.000 66 92
101002014-03-04 00:00:00.000 66 92
101002014-03-05 00:00:00.000 110 92
101002014-03-06 00:00:00.000 16 92
101002014-03-07 00:00:00.000 66 92
101002014-03-10 00:00:00.000 49 92
101002014-03-11 00:00:00.000 35 92
101022014-02-28 00:00:00.000 1 4912
101022014-03-02 00:00:00.000 2 4912
101022014-03-03 00:00:00.000 168 4912
101022014-03-04 00:00:00.000 3849 4912
101022014-03-05 00:00:00.000 684 4912
101022014-03-06 00:00:00.000 1421 4912
101022014-03-07 00:00:00.000 1902 4912
101022014-03-10 00:00:00.000 632 4912
101022014-03-11 00:00:00.000 455 4912
101022014-03-13 00:00:00.000 525 4912
101022014-03-17 00:00:00.000 84 4912
101022014-03-27 00:00:00.000 1 4912
101042014-03-04 00:00:00.000 320 143
101042014-03-05 00:00:00.000 62 143
101042014-03-06 00:00:00.000 10 143
101042014-03-07 00:00:00.000 32 143
CREATE TABLE #OverInv (Item VARCHAR(15), PromiseDate DATETIME, QtyOnHand INT, QtyOrdered INT, TotalOver INT)
DECLARE @Item VARCHAR(15),
@PromiseDate DATETIME,
@QtyOrdered INT,
@QtyOnHand INT,
@CurrentItem VARCHAR(15),
@TotalOver INT
SET @TotalOver = 0
SET @CurrentItem = 0
DECLARE rt_cursor CURSOR
FOR
SELECT Item, PromiseDate, QtyOrdered, QtyOnHand
FROM Inventory
OPEN rt_cursor
FETCH NEXT FROM rt_cursor INTO @Item,@PromiseDate,@QtyOrdered,@QtyOnHand
WHILE @@FETCH_STATUS = 0
IF @CurrentItem = 0
BEGIN
SET @CurrentItem = @Item
SET @TotalOver = (@QtyOnHand - @QtyOrdered)
IF @TotalOver < 1
BEGIN
INSERT #OverInv VALUES (@Item,@PromiseDate,@QtyOnHand,@QtyOrdered,@TotalOver)
FETCH NEXT FROM rt_cursor INTO @Item,@PromiseDate,@QtyOrdered,@QtyOnHand
SET @TotalOver = 0
END
ELSE
BEGIN
FETCH NEXT FROM rt_cursor INTO @Item,@PromiseDate,@QtyOrdered,@QtyOnHand
END
END
IF @CurrentItem = @Item
SET @TotalOver = (@TotalOver - @QtyOrdered)
BEGIN
IF @TotalOver < 1
BEGIN
INSERT #OverInv VALUES (@Item,@PromiseDate,@QtyOnHand,@QtyOrdered,@TotalOver)
FETCH NEXT FROM rt_cursor INTO @Item,@PromiseDate,@QtyOrdered,@QtyOnHand
SET @TotalOver = 0
END
ELSE
BEGIN
FETCH NEXT FROM rt_cursor INTO @Item,@PromiseDate,@QtyOrdered,@QtyOnHand
END
END
IF @CurrentItem <> @Item
SET @CurrentItem = @Item
SET @TotalOver = (@QtyOnHand - @QtyOrdered)
BEGIN
IF @TotalOver < 1
BEGIN
INSERT #OverInv VALUES (@Item,@PromiseDate,@QtyOnHand,@QtyOrdered,@TotalOver)
FETCH NEXT FROM rt_cursor INTO @Item,@PromiseDate,@QtyOrdered,@QtyOnHand
SET @TotalOver = 0
END
ELSE
BEGIN
FETCH NEXT FROM rt_cursor INTO @Item,@PromiseDate,@QtyOrdered,@QtyOnHand
END
END
CLOSE rt_cursor
DEALLOCATE rt_cursor
SELECT * FROM #OverInv ORDER BY Item
DROP TABLE #OverInv
Please Help!
Thank you
Ken
March 7, 2014 at 12:52 pm
I didn't spend a lot of time looking at the details of your question, but I think the top 3 results of this search on SSC will help you solve the problem. I particularly recommend the article by Jeff Moden.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply