Reputation: 21
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