Skip to content
Advertisement

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.

Advertisement

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.

1 People found this is helpful
Advertisement