Help querying meta count of SCD2 table

Transact-SQL
1

I can't really wrap my mind around how to do this. I have a table which is formatted as SCD2. I want to figure out how many days a given SKU had inventory. In this case, zero/non-zero matters but the amount does not. So from the 7th until the 15th it had zero stock. Again from the 17th until the 25th it had zero stock. In total 15/28 days the item was out of stock.

Unfortunately the max datetime doesn't actually tell me what day that month ends on. I also don't know how to count days within a list of datetimes. Can anyone point me in the right direction? This is what the dataset looks like:

CREATE TABLE #SCD2_TEMP (
	id INTEGER
	, dateadded DATETIME
	, dateexpired DATETIME
	, sku NVARCHAR(MAX)
	, qty INTEGER
);

INSERT INTO #SCD2_TEMP (id, dateadded, dateexpired, sku, qty) VALUES
('1','2019-01-30 11:11:05.000', '2019-02-07 14:38:44.007', 'ProductNumber001', '5')
, ('2','2019-02-07 12:38:44.007', '2019-02-15 14:38:44.007', 'ProductNumber001', '0')
, ('3','2019-02-15 14:38:44.007', '2019-02-17 19:24:52.753', 'ProductNumber001', '47')
, ('4','2019-02-17 19:24:52.797', '2019-02-25 22:35:58.110', 'ProductNumber001', '0')
, ('5','2019-02-25 22:35:58.147', '5000-01-01 00:00:00.000', 'ProductNumber001', '43');


SELECT * FROM #SCD2_TEMP
2

Are you sure the answers not 16 days?
i.e. 7 to 15 is 8 days and 17 to 25 is 8 days = 16 days

IF OBJECT_ID('tempdb..#SCD2_TEMP ') IS NOT NULL
DROP TABLE #SCD2_TEMP

CREATE TABLE #SCD2_TEMP (
id INTEGER
, dateadded DATETIME
, dateexpired DATETIME
, sku NVARCHAR(MAX)
, qty INTEGER
);

INSERT INTO #SCD2_TEMP (id, dateadded, dateexpired, sku, qty) VALUES
('1','2019-01-07 12:38:44.007', '2019-01-30 14:38:44.007', 'ProductNumber001', '0')
,('2','2019-01-30 11:11:05.000', '2019-02-07 14:38:44.007', 'ProductNumber001', '4')
, ('3','2019-02-07 12:38:44.007', '2019-02-15 14:38:44.007', 'ProductNumber001', '0')
, ('4','2019-02-15 14:38:44.007', '2019-02-17 19:24:52.753', 'ProductNumber001', '47')
, ('5','2019-02-17 19:24:52.797', '2019-02-25 22:35:58.110', 'ProductNumber001', '0')
, ('6','2019-02-25 22:35:58.147', '2021-01-01 00:00:00.000', 'ProductNumber001', '43');

WITH CTE AS (SELECT DATEADD(m, DATEDIFF(m, 0, MIN(dateadded)) - 1, 0) AS StartDate,GETDATE() AS EndDate,DATEADD(m, DATEDIFF(m, 0,MAX(dateexpired)) + 1, 0) AS MaxDate,1 AS Start FROM #SCD2_TEMP
UNION ALL
SELECT DATEADD(m,1,StartDate),DATEADD(m,2,StartDate) ,MaxDate, 2
FROM CTE
WHERE StartDate < MaxDate )
SELECT sku,SUM(Cnt) AS DaysOutOfStock,DaysInMonth,StartDate,EndDate
FROM (
SELECT T.sku,DATEDIFF( dd,
CASE
WHEN T.dateadded < CTE.StartDate THEN
CTE.StartDate
ELSE
T.dateadded
END,
CASE
WHEN T.dateexpired > CTE.EndDate THEN
CTE.EndDate
ELSE
T.dateexpired
END)
* (1 - CAST(T.qty AS BIT)) AS Cnt,DAY(CTE.EndDate) AS DaysInMonth,CTE.StartDate,CTE.EndDate
FROM #SCD2_TEMP T JOIN CTE ON (T.dateadded >= CTE.StartDate AND T.dateadded < CTE.EndDate) OR (T.dateexpired >= CTE.StartDate AND T.dateexpired < CTE.EndDate)
WHERE CTE.Start > 1
) Z
GROUP BY sku,DaysInMonth,StartDate,EndDate
HAVING SUM(Cnt) > 0
ORDER BY StartDate
OPTION (MAXRECURSION 0);

1 Like
3

Hey, I was in such a rush that I never thanked you for this. I really appreciate it. It helped me wrap my mind around what I needed to do & get to solving the problem.

1 Like