I have a table that tracks changes in inventory by date. But I need to do a calculation that takes into account how many days the inventory was at that value.
I am able to generate a list of all dates within a given range & have merged the two tables to identify which rows do not have data. I also think that I should be using LAG to identify the value of the day before. However, I cannot get it to work.
The idea being that if a day has a null value, I would fill in the inventory value of the day before. Can anyone help me finish this? Alternatively is there a better way to do this?
-- Create Dummy data
CREATE TABLE #tmp_inv ([ActivityDate] date, [ProductID] nvarchar(max), [TotalBinQty] int);
INSERT INTO #tmp_inv ([ActivityDate], [ProductID], [TotalBinQty]) VALUES
('2022-01-13', 'MyProduct', '2')
, ('2022-01-11', 'MyProduct', '0')
, ('2022-01-07', 'MyProduct', '302')
, ('2022-01-06', 'MyProduct', '302')
, ('2022-01-04', 'MyProduct', '5832')
, ('2022-01-03', 'MyProduct', '5833')
;
-- Declare parameters
DECLARE @StartDate datetime = '2022-01-01'
,@EndDate datetime = '2022-01-31'
;
-- Create list of all dates in range
WITH date_list AS
(SELECT CAST( @StartDate AS DATE) as [Date]
UNION ALL
SELECT CAST( DATEADD(day, 1, [Date]) AS DATE )
FROM date_list
WHERE DATEADD(day, 1, [Date]) <= @EndDate
)
-- Merge Available data with all dates
SELECT
[dli].[Date]
, [gbd].[ProductID]
, [gbd].[TotalBinQty]
, (LAG([TotalBinQty]) OVER (
PARTITION BY [ProductID], [ActivityDate]
ORDER BY [ActivityDate])
) AS [YesterdaysTotalBinQty]
FROM date_list dli
FULL JOIN #tmp_inv gbd
ON [dli].[Date] = [gbd].[ActivityDate]
ORDER BY [dli].[Date] DESC