1

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]
pawe84
  • 21
  • 2
  • Don't you think a case like this is better manipulated in the application program (the front end) where the data will be inputted? – CCCC Sep 23 '21 at 15:01
  • I need this structure for external bi software, that's why I'm using sql. – pawe84 Sep 24 '21 at 07:02
  • I just finished developing a software for Supply Chain management of which inventory is part of. I still think you can do your manipulations in your frontend application and save the the data in your Sql database – CCCC Sep 24 '21 at 07:15

0 Answers0