SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Help creating query the that returns running total
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kpeck
Starting Member

13 Posts

Posted - 03/07/2014 :  15:00:02  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000