SQL Query to calculate daily inventory over a date range

Question

I am working in Azure Synapse and trying to create a query to calculate the daily quantity of each item for each day in a date range. The starting qty should include the sum of all entries before that date. For Example, if we want to see daily inventory from 2023-01-01 through 2023-01-06, the first row that would be 2023-01-01 would contain the sum of the items Qty from the start date plus anything before that start date.

Below is an example of my table: Inventory

ItemId	Date	Qty
A002	2022-12-10	1
A001	2022-12-30	4
A001	2023-01-01	5
A001	2023-01-01	-2
A001	2023-01-03	4
A002	2023-01-02	3
A002	2023-01-05	2
A002	2023-01-06	-1

Using the example table would expect an outcome of the below

ItemId	Date	Qty
A001	2023-01-01	7
A001	2023-01-02	7
A001	2023-01-03	11
A001	2023-01-04	11
A001	2023-01-05	11
A001	2023-01-06	11
A002	2023-01-01	1
A002	2023-01-02	4
A002	2023-01-03	4
A002	2023-01-04	4
A002	2023-01-05	6
A002	2023-01-06	5

Below is the latest query I have tried and I seem to be getting the starting qty fine but looks dates after are not and I am trying to get an entry for each day.

WITH Inventory AS (
    SELECT ItemId, Date, SUM(Qty) AS TotalQuantity
    FROM Inventory
    WHERE Date <= '2023-01-07'
    GROUP BY ItemId, Date
), StartingInventory AS (
    SELECT ItemId, SUM(Qty) AS StartingQuantity
    FROM Inventory
    WHERE DatePhysical < '2023-01-01'
    GROUP BY ItemId
)
SELECT i.ItemId, i.Date, SUM(i.TotalQuantity) + 
COALESCE(s.StartingQuantity, 0) AS DailyTotalInventory
FROM Inventory i
LEFT JOIN StartingInventory s ON i.ItemId = s.ItemId
WHERE i.DatePhysical BETWEEN '2023-01-01' AND '2023-01-07'
GROUP BY i.ItemId, i.Date, s.StartingQuantity
ORDER BY i.ItemId, i.Date

And another attempt at this query seem to get the starting date calculating correctly however following dates are not and a row is not generated for each date in the date range

    WITH AllDates AS (
    SELECT DISTINCT d.DatePhysical, i.ItemId
    FROM Inventrans i
    CROSS JOIN (
        SELECT DISTINCT DatePhysical
        FROM InvenTrans
        WHERE DatePhysical BETWEEN '2023-01-01' AND '2023-01-07'
    ) d
), Inventory AS (
    SELECT ItemId, DatePhysical, SUM(Qty) AS TotalQuantity
    FROM InvenTrans
    WHERE DatePhysical <= '2023-01-07'
    GROUP BY ItemId, DatePhysical
), StartingInventory AS (
    SELECT ItemId, SUM(Qty) AS StartingQuantity
    FROM Inventrans
    WHERE DatePhysical < '2023-01-01'
    GROUP BY ItemId
)
SELECT d.DatePhysical, d.ItemId, COALESCE(SUM(i.TotalQuantity), 0) + COALESCE(s.StartingQuantity, 0) AS DailyTotalInventory
FROM AllDates d
LEFT JOIN Inventory i ON d.ItemId = i.ItemId AND d.DatePhysical = i.DatePhysical
LEFT JOIN StartingInventory s ON d.ItemId = s.ItemId
WHERE d.DatePhysical BETWEEN '2023-01-01' AND '2023-01-07'
GROUP BY d.DatePhysical, d.ItemId, s.StartingQuantity
ORDER BY d.ItemId, d.DatePhysical

and where is your attempt? – OldProgrammer May 04 '23 at 22:01 — OldProgrammer, May 04 '23 at 22:01
sorry just updated :) – Zachary Roberts May 04 '23 at 22:22 — Zachary Roberts, May 04 '23 at 22:22

Aswin · Answer 1 · 2023-05-08T03:25:34.753

With  Inventory AS (
    SELECT ItemId, DatePhysical, SUM(Qty) AS TotalQuantity
    FROM Inventrans
    WHERE DatePhysical <= '2023-01-07'
    GROUP BY ItemId, DatePhysical
), StartingInventory AS (
    SELECT ItemId,DatePhysical='2023-01-01',SUM(Qty) AS StartingQuantity
    FROM Inventrans
    WHERE DatePhysical < '2023-01-01'
    GROUP BY ItemId
),AllDates as(
    select DatePhysical=cast('2023-01-01' as date)
    UNION ALL
    SELECT dateadd(day,1,[DatePhysical]) from AllDates where DatePhysical<'2023-01-07'
), AllIDDate as(
select distinct itemid,j.DatePhysical from Inventory i cross apply AllDates j 
)

select j.ItemId,j.DatePhysical,coalesce(sum(i.TotalQuantity) over (partition by j.itemId order by j.DatePhysical),0)+ 
COALESCE(s.StartingQuantity, 0) AS DailyTotalInventory
from AllIDDate j 
LEFT JOIN Inventory i on i.ItemId = j.ItemId and i.DatePhysical=j.DatePhysical
LEFT JOIN StartingInventory s ON j.ItemId = s.ItemId and j.DatePhysical=s.DatePhysical
WHERE j.DatePhysical BETWEEN '2023-01-01' AND '2023-01-07'

db<>fiddle

This query first creates a CTE called Inventory that groups the inventory transactions by item and date and calculates the total quantity for each day before January 7,2023. Then, it creates another CTE called StartingInventory that detemines the total quantity of each item before January 1, 2023. Next, it creates a CTE called AllDates that generates a list of all dates between January 1, 2023, and January 7, 2023. It then creates a CTE called AllIDDate that generates a list of all item IDs and dates between January 1, 2023, and January 7, 2023. Finally, it joins the AllIDDate CTE with the Inventory and StartingInventory CTEs to calculate the daily total inventory for each item.

Input:

ItemId	DatePhysical	Qty
1	2023-01-01	10
1	2023-01-01	5
1	2023-01-03	15
1	2023-01-03	10
1	2023-01-05	10
1	2023-01-05	5
2	2022-03-28	10
2	2022-01-31	20
2	2022-01-31	10
2	2023-01-02	15
2	2023-01-02	25
2	2023-01-02	30
2	2023-01-04	20
2	2023-01-04	15
2	2023-01-04	25

Output:

ItemId	DatePhysical	DailyTotalInventory
1	2023-01-01	15
1	2023-01-02	15
1	2023-01-03	40
1	2023-01-04	40
1	2023-01-05	55
1	2023-01-06	55
1	2023-01-07	55
2	2023-01-01	40
2	2023-01-02	70
2	2023-01-03	70
2	2023-01-04	130
2	2023-01-05	130
2	2023-01-06	130
2	2023-01-07	130

Thanks for the reply @Aswin, This is very close! And this is where my query may be getting little odd but for example itemId 1 in your output should have a DailyTotalInventory of 15 on 2023-01-02 even if there wasn't an entry for it there was just no changes to its quantity so each date after the start date should also be using the previous days value to SUM its value — Zachary Roberts, May 05 '23 at 10:57
Could you share the expected output in the table format? So that it could be easy to understand — Aswin, May 05 '23 at 11:26
Yes in my post should be an example table and an expected output table :) I can add another example if needed — Zachary Roberts, May 05 '23 at 13:40
Check the fiddle link shared in above comment. Check if that query solves your requirement. — Aswin, May 05 '23 at 16:52
Thanks Aswin, hope you had a good weekend - I checked out the fiddle and it is very close but seems to be having some issues. Please see this [fiddle](https://dbfiddle.uk/uYirmkuz) - I added a value before 01-01-23 to see if the query would also calculate quantities before the start date and it does do that which is good however on 01-02-2023 the qty drops to 5 when it should have stayed at 15 — Zachary Roberts, May 08 '23 at 12:26
Check this [fiddle2](https://dbfiddle.uk/eHib9anv) once. I have changed the code here — Aswin, May 09 '23 at 13:05

score 0 · Answer 2 · answered May 05 '23 at 11:26

with period(d1, d2) as (select cast('2023-01-01' as date), cast('2023-01-06' as date)),
sums(itemid, dt, qty) as (
  select itemid, dt, sum(qty) 
  from (
    select itemid, case when datephysical < d1 then d1 else datephysical end dt, qty 
    from inventory join period on datephysical <= d2 ) i
  group by itemid, dt ),
range(dt) as (
  select d1 from period union all 
  select dateadd(d, 1, dt) from range join period on dt < d2)
select it.itemid, range.dt, 
       coalesce(sum(qty) over (partition by it.itemid order by range.dt), 0) qty 
from (select distinct itemid from sums) it cross apply range
left join sums on sums.dt = range.dt and sums.itemid = it.itemid

dbfiddle demo

Subquery period - here start and end date are defined, sums - daily sums and if date is less than start it is treated as start date, range - date generator for given period.

These subqueries are joined and analytic sum does the rest of the job.

Thanks! This does get to my desired output but the issue I am now running into seems to be related to Azure Synapse SQL - get this error when trying 'The query references an object that is not supported in distributed processing mode.' — Zachary Roberts, May 05 '23 at 13:48
I don't know Azure, but after reading some docs I suspect that date range generator. This is simple recursive subquery generating several dates, like [here](https://stackoverflow.com/questions/23290454/get-all-dates-between-two-dates-in-sql-server). Maybe you have calendar table or any table from which you can take several rows and just add starting date + rownumber and use it instead `range`. — Ponder Stibbons, May 05 '23 at 14:32

SQL Query to calculate daily inventory over a date range

2 Answers2