Let me start with explaining the data and the problem. We have a data table that is transactional detail and a table called TimeSortOrder30 which list each of the 30 min intervals in a day with columns for Hour, Min, CustomDateSort. The CutsomDateSort is so we can have the first value be when the store opens at 6am and move forward from there wrapping around to the next day. That is the point behind the Dateadd – 6 hours bit.
It is very common for our customers to have multiple transactions in a day and we are trying to show the unique number of customer so far that day at each 30 min interval. The number of transactions in a day could easily be in the hundreds of millions so the dataset is quite large. Which is why I tried temp tables first. I have tried this with both CTE and with Temp tables and the code just runs for hours and never finishes.
I am having trouble figuring out the right way to get this code to work. If I could use distinct in a window function it would be real easy. But since we can not I have attempted this.
DROP TABLE IF EXISTS #TripPrep;
SELECT
DATEADD(MINUTE, 30 * (DATEDIFF(MINUTE, 0, StartTime) / 30), 0) AS IntervalStartTime
,CAST(Dateadd(HH,-6,StartTime) AS DATE) AS Date
,DATEPART(HH, DATEADD(MINUTE, 30 * (DATEDIFF(MINUTE, 0, StartTime) / 30), 0)) AS I_Hour
,DATEPART(MI, DATEADD(MINUTE, 30 * (DATEDIFF(MINUTE, 0, StartTime) / 30), 0)) AS I_Min
,Cust_ID
INTO
#TripPrep
FROM DataTable
WHERE
StoreID = 1
AND StartTime >= '2024-07-09 06:00:00 -05:00';
DROP TABLE IF EXISTS #TripPrepWithSortOrder;
SELECT
TP.IntervalStartTime
,TP.Date
,TP.I_Hour
,TP.I_Min
,TP.Cust_ID
,TSO.CustomDateSort
INTO
#TripPrepWithSortOrder
FROM #TripPrep AS TP
JOIN dbo.TimeSortOrder30 AS TSO
ON TP.I_Hour = TSO.Hour
AND TP.I_Min = TSO.Min;
DROP TABLE IF EXISTS #DistinctTrips;
SELECT
TP1.IntervalStartTime
,TP1.Date
,TP1.I_Hour
,TP1.I_Min
,TP1.CustomDateSort
,COUNT(DISTINCT TP2.Cust_ID) AS Trips
INTO
#DistinctTrips
FROM #TripPrepWithSortOrder AS TP1
JOIN #TripPrepWithSortOrder AS TP2
ON TP1.Date = TP2.Date
AND TP1.CustomDateSort >= TP2.CustomDateSort
GROUP BY
TP1.IntervalStartTime
,TP1.Date
,TP1.I_Hour
,TP1.I_Min
,TP1.CustomDateSort;
SELECT
DistinctTrips.IntervalStartTime
,DistinctTrips.Date
,DistinctTrips.I_Hour
,DistinctTrips.I_Min
,DistinctTrips.CustomDateSort
,DistinctTrips.Trips
FROM #DistinctTrips
ORDER BY
DistinctTrips.IntervalStartTime;
WITH TripPrep
AS
(
SELECT
DATEADD(MINUTE, 30 * (DATEDIFF(MINUTE, 0, StartTime) / 30), 0) AS IntervalStartTime
,CAST(Dateadd(HH,-6,StartTime) AS DATE) AS "Date"
,DATEPART(HH, DATEADD(MINUTE, 30 * (DATEDIFF(MINUTE, 0, StartTime) / 30), 0)) AS I_Hour
,DATEPART(MI, DATEADD(MINUTE, 30 * (DATEDIFF(MINUTE, 0, StartTime) / 30), 0)) AS I_Min
,Cust_ID
FROM DataTable
WHERE
StoreID = 1
AND StartTime >= '2024-07-09 06:00:00 -05:00'
)
,TripPrepWithSortOrder
AS
(
SELECT
TP.IntervalStartTime
,TP.[Date]
,TP.I_Hour
,TP.I_Min
,TP.Cust_ID
,TSO.CustomDateSort
FROM TripPrep AS TP
JOIN dbo.TimeSortOrder30 AS TSO
ON TP.I_Hour = TSO.Hour
AND TP.I_Min = TSO.Min
)
,DistinctTrips
AS
(
SELECT
TP1.IntervalStartTime
,TP1.[Date]
,TP1.I_Hour
,TP1.I_Min
,TP1.CustomDateSort
,COUNT(DISTINCT TP2.Cust_ID) AS Trips
FROM TripPrepWithSortOrder AS TP1
JOIN TripPrepWithSortOrder AS TP2
ON TP1.[Date] = TP2.[Date]
AND TP1.CustomDateSort >= TP2.CustomDateSort
GROUP BY
TP1.IntervalStartTime
,TP1.[Date]
,TP1.I_Hour
,TP1.I_Min
,TP1.CustomDateSort
)
SELECT
DistinctTrips.IntervalStartTime
,DistinctTrips.[Date]
,DistinctTrips.I_Hour
,DistinctTrips.I_Min
,DistinctTrips.CustomDateSort
,DistinctTrips.Trips
FROM DistinctTrips
ORDER BY
DistinctTrips.IntervalStartTime;
I found a way to make it works and it runs quite fast for me. Under 30 seconds.
DECLARE @StartDate DATETIME = DATEADD(HH, 7, CAST(CAST(GETDATE() AS DATE) AS DATETIME));
DECLARE @EndDate DATETIME = DATEADD(DD, 1, @StartDate);
Drop Table If Exists #Testing
SELECT
DATEADD(MINUTE, 30 * (DATEDIFF(MINUTE, 0, StartTime) / 30), 0) AS IntervalStartTime
,CAST(DATEADD(HH,-6,StartTime) AS DATE) AS Date
,DATEPART(HH, DATEADD(MINUTE, 30 * (DATEDIFF(MINUTE, 0, StartTime) / 30), 0)) AS I_Hour
,DATEPART(MI, DATEADD(MINUTE, 30 * (DATEDIFF(MINUTE, 0, StartTime) / 30), 0)) AS I_MIn
,Cust_ID
INTO #Testing
FROM ODS.dbo.VW_CDS_StatDetail AS CSd
WHERE
Store_ID = 1
AND cast(StartTime as datetime) >= @StartDate
AND cast(StartTime as datetime) < @EndDate;
Drop Table If Exists #TripPrep2
SELECT
TP.IntervalStartTime
,TP.Date
,TP.I_Hour
,TP.I_MIn
,TP.Cust_ID
,TSO.CustomDateSort
INTO #TripPrep2
FROM #Testing AS TP
JOIN dbo.TimeSortOrder30 AS TSO
ON TP.I_Hour = TSO.Hour
AND TP.I_MIn = TSO.Min;
SELECT
CASE
WHEN TSO.CustomDateSort >36 THEN DATEADD(DD,1,DATEADD(HH,TSO.Hour,DATEADD(MI,TSO.Min,CAST(TP.Date AS DATETIME))))
ELSE DATEADD(HH,TSO.Hour,DATEADD(MI,TSO.Min,CAST(TP.Date AS DATETIME)))
END AS IntervalStartTime
,TSO.CustomDateSort
,COUNT(DISTINCT TP.Cust_ID)
FROM #TripPrep2 AS TP
CROSS APPLY dbo.TimeSortOrder30 AS TSO
WHERE Tp.CustomDateSort <= TSO.CustomDateSort
GROUP BY CASE
WHEN TSO.CustomDateSort > 36 THEN DATEADD(DD, 1, DATEADD(HH, TSO.Hour, DATEADD(MI, TSO.Min, CAST(TP.Date AS DATETIME))))
ELSE DATEADD(HH, TSO.Hour, DATEADD(MI, TSO.Min, CAST(TP.Date AS DATETIME)))
END
,TSO.CustomDateSort
ORDER BY TSO.CustomDateSort