pawe84
pawe84

Reputation: 21

sql query issue: warehouse aging inventory when negative entries

I found a warehouse aging inventory example online (see modified code below). Everything works fine if the entry type for purchase (0) is positive and for sales (1) is negative. But if the values are inverse (because of cancellation) then the results will be wrong.

Example: There are four entries, three of them are purchase entries but as you can see the second one has been canceled that's why the quantity is negative. The total sum of column RemainingQty must be 0 in that case but result is 1699.

What do I have to change in my SQL query?

Thanks for any advice.

DECLARE @ItemLedgerEntry TABLE
     (
       id INT IDENTITY(1, 1)  NOT NULL PRIMARY KEY ,
       ItemNo INT NOT NULL, --references another item
       Qty FLOAT NOT NULL, --quantity
       EntryType INT NOT NULL, --type=0 bought, type=1 sold
       PostingDate DATETIME NOT NULL -- transaction date
     );
     
 INSERT  @ItemLedgerEntry
         ( ItemNo, qty, EntryType, PostingDate )
 VALUES  ( 1999,  1700,  0, '10-06-2021'),
         ( 1999,  -1700, 0, '29-06-2021'),
         ( 1999,       1,  0, '03-08-2021'),
         ( 1999,      - 1,  1, '09-08-2021');

                      
WITH    Sold
          AS ( SELECT   IT.[ItemNo] ,
                        SUM(IT.Qty) AS TotalSoldQty
               FROM     @ItemLedgerEntry IT
               WHERE    It.[EntryType] =1    
               GROUP BY ItemNo            
             ),
        Bought
          AS ( SELECT   IT.* ,
                        (
                SELECT  SUM(RS.Qty)
                FROM    @ItemLedgerEntry RS
                WHERE   RS.[EntryType]  =0 AND RS.[ItemNo] = IT.[ItemNo] AND RS.[PostingDate] <= IT.[PostingDate]
                       
                        ) AS RunningBoughtQty
               FROM     @ItemLedgerEntry IT
               WHERE    IT.[EntryType] = 0 
             )
    

    SELECT  
    B.[ItemNo], 
    B.[PostingDate], 
    B.[EntryType],
    S.TotalSoldQty,
    B.RunningBoughtQty,
    B.RunningBoughtQty + S.TotalSoldQty AS RunningDifferenceQty,        
    CASE WHEN  (B.RunningBoughtQty) + (S.TotalSoldQty) <0
            THEN 0
            ELSE B.RunningBoughtQty + S.TotalSoldQty
    END AS  RunningRemainingQty,
        
    CASE  WHEN B.RunningBoughtQty + S.TotalSoldQty < 0 THEN 0 
          WHEN B.RunningBoughtQty + S.TotalSoldQty > B.Qty THEN B.Qty
             ELSE B.RunningBoughtQty + S.TotalSoldQty           
    END AS  RemainingQty

    FROM    Bought B
        inner JOIN Sold S ON B.[ItemNo] = S.[ItemNo]

Upvotes: 1

Views: 99

Answers (0)

Related Questions