Help creating query the that returns running total

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.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Help creating query the that returns running total

Author

Topic

kpeck
Starting Member

13 Posts

Posted - 2014-03-07 : 15:00:02

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
10100 2014-03-03 00:00:00.000 66 92
10100 2014-03-04 00:00:00.000 66 92
10100 2014-03-05 00:00:00.000 110 92
10100 2014-03-06 00:00:00.000 16 92
10100 2014-03-07 00:00:00.000 66 92
10100 2014-03-10 00:00:00.000 49 92
10100 2014-03-11 00:00:00.000 35 92
10102 2014-02-28 00:00:00.000 1 4912
10102 2014-03-02 00:00:00.000 2 4912
10102 2014-03-03 00:00:00.000 168 4912
10102 2014-03-04 00:00:00.000 3849 4912
10102 2014-03-05 00:00:00.000 684 4912
10102 2014-03-06 00:00:00.000 1421 4912
10102 2014-03-07 00:00:00.000 1902 4912
10102 2014-03-10 00:00:00.000 632 4912
10102 2014-03-11 00:00:00.000 455 4912
10102 2014-03-13 00:00:00.000 525 4912
10102 2014-03-17 00:00:00.000 84 4912
10102 2014-03-27 00:00:00.000 1 4912
10104 2014-03-04 00:00:00.000 320 143
10104 2014-03-05 00:00:00.000 62 143
10104 2014-03-06 00:00:00.000 10 143
10104 2014-03-07 00:00:00.000 32 143

Please Help if you can.
Thank you

Ken

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources