SUMIF scenario in SQL

Transact-SQL
1

This is one of things very hard to explain in words. The data here is simple, it seems requirement is not. On hand qty for a product is grouped by "Best Before Date". Sales qty with a ReqDate before the Best Before Date should be deducted from the on hand Qty.
But the requirement is it get deducted from the earliest Best Before Date. In this example 121 units should be paired with best before of 2025-04-14 and ZERO for 2025-04-15.

I could solve this easily if the on-hand could be grouped by item showing only the Max('Best Before Date') but the requirement is to show all the different best before dates.

Someone has an excel using SUMIFS that seems to be working but I need to recreate in SQL or Power Query. Looking at their method in excel (which I am not showing here) I don't under how excel is NOT throwing a circular reference error and sure enough my attempts to recreate in PBI or Power query have resulted in circular reference errors. So I am trying solve this in SQL before I bring the data into PBI.

help
help1006×361 58.6 KB

2

To calculate a running total in SQL, you can utilize the SUM function in combination with the OVER clause. An example query might look like the following:

SELECT
itemID,
Reqdate,
sales_orders,
SUM(sales_orders) OVER (PARTITION BY itemID ORDER BY Reqdate) AS RunningTotal
FROM ...

If you need additional assistance, feel free to share some sample data to provide more context. For further reading, you can refer to the official Microsoft Learn documentation: OVER Clause (Transact-SQL) - SQL Server | Microsoft Learn

3

hi

hope this helps

;WITH SalesAssignment AS (
    SELECT 
        s.ItemID,
        s.ReqDate,
        s.ReqQty,
        bb.Date AS BestBeforeDate,
        ROW_NUMBER() OVER (
            ORDER BY bb.Date ASC
        ) AS RowNum
    FROM 
        [FGMonthlyRiskSales] s
    CROSS JOIN 
        [FGMonthlyRiskOnHand] bb
    WHERE 
        s.ItemID = '193222'
        AND s.ReqDate < bb.Date
),
Deductions AS (
    SELECT 
        BestBeforeDate,
        SUM(ReqQty) AS TotalDeduction
    FROM (
        SELECT 
            BestBeforeDate,
            ReqQty,
            ROW_NUMBER() OVER (
                PARTITION BY BestBeforeDate 
                ORDER BY ReqDate ASC
            ) AS DeductionRow
        FROM SalesAssignment
    ) AS DeductionRows
    WHERE DeductionRow <= 1  -- Only first deduction
    GROUP BY BestBeforeDate
)
SELECT 
    bb.Date AS BestBeforeDate,
    bb.OnHand - COALESCE(d.TotalDeduction, 0) AS RemainingQty
FROM 
    [FGMonthlyRiskOnHand] bb
LEFT JOIN 
    Deductions d ON bb.Date = d.BBDate
WHERE 
    bb.ItemID = '193222'
ORDER BY 
    bb.Date ASC;
4

hi

create sample data

-- Create Inventory table
CREATE TABLE Inventory (
    ProductID INT,
    BestBeforeDate DATE,
    OnHand INT
);

-- Create Sales table
CREATE TABLE Sales (
    ReqDate DATE,
    ProductID INT,
    SalesQty INT
);

-- Insert sample data into Inventory table
INSERT INTO Inventory (ProductID, BestBeforeDate, OnHand)
VALUES 
(1, '2025-04-14', 100),
(1, '2025-04-15', 500),
(2, '2025-04-12', 200),
(2, '2025-04-20', 300);

-- Insert sample data into Sales table
INSERT INTO Sales (ReqDate, ProductID, SalesQty)
VALUES 
('2025-04-10', 1, 121),
('2025-04-15', 2, 150);