Generate time slots

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