Here’s an Inline Table Valued Function (TVF) for generating time-slots from a start-date to an end-date, given a certain time for each slot, given in minutes.
This would be useful for many applications, like scheduling systems, sales statistics broken down into certain slices of time etc. The function does have some limitations, eg there can’t be more than 100.000 minutes between start and endtime. This is easily fixed by just adding Another CROSS JOIN to CTE2, or by changing the DATEADD-functions to use hour instead of minute if that fits your purpose.
The function uses a numbers table together with DATEADD to genereate time slots. I’ve seen tons of Numbers table solutions, using CROSS JOIN and the table sys.columns. This solution is faster, since it only holds the numbers table in memory and won’t have to do any logical reads or Scans against sys.columns. My solution uses zero logical reads, compared to 12 logical reads using 3600 time-slots using sys.columns CROSS JOIN sys.columns. Not a dramatic difference, but if you CROSS APPLY this function with some large dataset you might end up with a more dramatic performance difference.
Anyway, here’s the code for the function.
CREATE FUNCTION GetTimeSlots(@starttime datetime, @endtime datetime, @interval int) RETURNS TABLE AS RETURN --Generate numbers 0 to N-1 for number of slots N WITH cte AS( SELECT 1 AS n UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), cte2 AS ( SELECT TOP(DATEDIFF(minute,@starttime, @endtime) / @interval) row_number() OVER(ORDER BY c1.n)-1 AS rownum FROM cte c1 CROSS JOIN cte c2 CROSS JOIN cte c3 CROSS JOIN cte c4 CROSS JOIN cte c5 ) SELECT DATEADD(minute,rownum*@interval,@starttime) AS slotfrom, DATEADD(minute,(rownum+1)*@interval,@starttime) AS slotto FROM cte2
I find this slightly more useful since it’s useful for a BETWEEN without overlapping
DATEADD(MS, -3, DATEADD(minute,(rownum+1)*@interval,@starttime)) AS slotto