SQL Server – current inventory with physical count

I feel as though I’m missing something extremely fundamental that should be obvious. I’m basically trying to take the below data and calculate a fifth column: Inventory_Current.

Example data:

DateStamp	ProductID	Inventory_Change	Inventory_Count
2021-07-01	100	-300	100000
2021-07-01	200	-700	50000
2021-07-02	100	200	null
2021-07-02	200	-100	null
2021-07-03	100	500	null
2021-07-03	200	300	null
2021-07-04	100	-1000	99500
2021-07-04	200	-100	null
2021-07-05	100	100	null
2021-07-05	200	300	50500

Inventory_Count is generally only performed once a month, but sales dictate that Inventory_Change happens daily. So I need to calculate current inventory levels based on the sum of the Inventory_Changes since the last Inventory_Count per ProductID. The Example data is meant to succinctly capture this concept.

Expected result:

DateStamp	ProductID	Inventory_Change	Inventory_Count	Inventory_Current
2021-07-01	100	-300	100000	99700
2021-07-01	200	-700	50000	49300
2021-07-02	100	200	null	99900
2021-07-02	200	-100	null	49200
2021-07-03	100	500	null	100400
2021-07-03	200	300	null	49500
2021-07-04	100	-1000	99500	98500
2021-07-04	200	-100	null	49400
2021-07-05	100	100	null	98600
2021-07-05	200	300	50500	50800

To calculate Inventory_Current, I’d roughly follow the below logic:

select
  DateStamp,
  ProductID,
  Inventory_Change,
  Inventory_Count,
  iif(Inventory_Count is not null,
    Inventory_Count+Inventory_Change,
    /*Do magic here*/
  ) as Inventory_Current
from
  Inventory

I’ve considered Itzik Ben-Gan’s Last non Null concept (brilliant, btw), leveraging LAG, OVER PARTITION, or giving up (SQL dba is not my primary role, obviously). It seems the big issue is all the solutions rely on a primary key, whereas my combination of DateStamp and ProductID seems to cause issues.

Even a link to some solid reading would be appreciated. Or a good night’s rest away from children. 🙂

Thanks in advance.

Answer

Without expected results (at the time of writing), this is a guess, however, I am going to guess that you want to have a cumulative SUM of the value of InventoryChange and add that to the last non-NULL value of InventoryCount. If so then perhaps this is what you are after:

CREATE TABLE dbo.YourTable (DateStamp date,
                            ProductID int,
                            InventoryChange int,
                            InventoryCount int);
GO
INSERT INTO dbo.YourTable
VALUES('2021-07-01',100,-300 , 100000),
      ('2021-07-01',200,-700 , 50000),
      ('2021-07-02',100,200  ,null),
      ('2021-07-02',200,-100 , null),
      ('2021-07-03',100,500  ,null),
      ('2021-07-03',200,300  ,null),
      ('2021-07-04',100,-1000,  99500),
      ('2021-07-04',200,-100 , null),
      ('2021-07-05',100,100  ,null),
      ('2021-07-05',200,300  ,50500);
GO
WITH Groups AS(
    SELECT DateStamp,
           ProductID,
           InventoryChange,
           InventoryCount,
           COUNT(InventoryCount) OVER (PARTITION BY ProductID ORDER BY DateStamp) AS Grp
    FROM dbo.YourTable)
SELECT DateStamp,
       ProductID,
       InventoryChange,
       CASE WHEN InventoryCount IS NOT NULL THEN InventoryCount + InventoryChange
            ELSE MAX(InventoryCount) OVER (PARTITION BY ProductID,Grp) +
                 SUM(InventoryChange) OVER (PARTITION BY ProductID,Grp ORDER BY DateStamp)
       END AS InventoryCount
FROM Groups
ORDER BY DateStamp;

GO

DROP TABLE dbo.YourTable;

db<>fiddle

If you don’t want the value of InventoryChange to be counted on the row with the non-NULL value, you’ll need a CASE expression inside the SUM.