FREE AI-Enhanced Online SQL Server Compiler

Untitled*

You must execute the code again to save those values in the database.

declare @PurchaseActions table
                         (
                             PurchaseId   bigint,
                             PurchaseDate datetime,
                             LineId       bigint,
                             ItemId       bigint,
                             Quantity     float,
                             Price        float
                         )

declare @SaleActions table
                     (
                         SaleId   bigint,
                         SaleDate datetime,
                         LineId   bigint,
                         ItemId   bigint,
                         Quantity float
                     )
                     
INSERT INTO @PurchaseActions(PurchaseId, PurchaseDate, LineId, ItemId, Quantity, Price) VALUES (21904, N'2024-08-01 12:52:31.053', 23649, 14558, 60, 7.7);
INSERT INTO @PurchaseActions(PurchaseId, PurchaseDate, LineId, ItemId, Quantity, Price) VALUES (21905, N'2024-08-05 12:53:52.753', 23656, 14558, 5, 9);

INSERT INTO @SaleActions(SaleId, SaleDate, LineId, ItemId, Quantity) VALUES (3741, N'2024-08-13 13:38:38.950', 24872, 14558, 63);

WITH RankedPurchases AS (SELECT PurchaseId,
                                PurchaseDate,
                                LineId                                                                                AS PurchaseLineId,
                                ItemId,
                                Quantity,
                                Price,
                                SUM(Quantity) OVER (PARTITION BY ItemId ORDER BY PurchaseDate, PurchaseId)            AS CumulativePurchaseQuantity,
                                SUM(Quantity) OVER (PARTITION BY ItemId ORDER BY PurchaseDate, PurchaseId) - Quantity AS PreviousCumulativePurchaseQuantity
                         FROM @PurchaseActions),
     RankedSales AS (SELECT SaleId,
                            SaleDate,
                            LineId                                                                        AS SaleLineId,
                            ItemId,
                            Quantity,
                            SUM(Quantity) OVER (PARTITION BY ItemId ORDER BY SaleDate, SaleId)            AS CumulativeSaleQuantity,
                            SUM(Quantity) OVER (PARTITION BY ItemId ORDER BY SaleDate, SaleId) - Quantity AS PreviousCumulativeSaleQuantity
                     FROM @SaleActions),
     MatchedSales AS (SELECT s.SaleId,
                             s.SaleLineId,
                             p.PurchaseId,
                             p.PurchaseLineId,
                             s.ItemId,
                             s.Quantity                                                                                    AS SaleQuantity,
                             p.Quantity                                                                                    AS PurchaseQuantity,
                             p.Price,
                             s.PreviousCumulativeSaleQuantity,
                             p.PreviousCumulativePurchaseQuantity,
                             ROW_NUMBER() OVER (PARTITION BY s.SaleId, s.SaleLineId ORDER BY p.PurchaseDate, p.PurchaseId) AS RowNum
                      FROM RankedSales s
                               JOIN RankedPurchases p ON s.ItemId = p.ItemId
                      WHERE p.CumulativePurchaseQuantity > s.PreviousCumulativeSaleQuantity
                        AND p.PreviousCumulativePurchaseQuantity < s.CumulativeSaleQuantity)
SELECT SaleId,
       SaleLineId,
       PurchaseId,
       PurchaseLineId,
       CASE
           WHEN RowNum = 1 THEN
               CASE
                   WHEN SaleQuantity <= PurchaseQuantity THEN SaleQuantity
                   ELSE PurchaseQuantity
                   END
           ELSE
               CASE
                   WHEN SaleQuantity - (PreviousCumulativeSaleQuantity - PreviousCumulativePurchaseQuantity) <= PurchaseQuantity
                       THEN SaleQuantity - (PreviousCumulativeSaleQuantity - PreviousCumulativePurchaseQuantity)
                   ELSE PurchaseQuantity
                   END
           END AS Quantity,
       Price
FROM MatchedSales
ORDER BY ItemId, SaleId, SaleLineId, RowNum

SaleId	SaleLineId	PurchaseId	PurchaseLineId	Quantity	Price
3741	24872	21904	23649	60.0	7.7000000000000002
3741	24872	21905	23656	5.0	9.0

(1 rows affected)

(1 rows affected)

(1 rows affected)

(2 rows affected)