Is there a more elegant way to write this cross join to create 5 minute intervals over 24 hours using a CTE? I need to write the same query for 1 minute intervals and I am not looking forward to it.
Code:
SELECT CAST ((CAST(Q1.HH AS VARCHAR(2)) + ':' + CAST(Q2.MM AS VARCHAR(2)) + ':00') AS TIME)
FROM
(
-- HOUR
SELECT 0 AS HH
UNION
SELECT 1
UNION
SELECT 2
UNION
SELECT 3
UNION
SELECT 4
UNION
SELECT 5
UNION
SELECT 6
UNION
SELECT 7
UNION
SELECT 8
UNION
SELECT 9
UNION
SELECT 10
UNION
SELECT 11
UNION
SELECT 12
UNION
SELECT 13
UNION
SELECT 14
UNION
SELECT 15
UNION
SELECT 16
UNION
SELECT 17
UNION
SELECT 18
UNION
SELECT 19
UNION
SELECT 20
UNION
SELECT 21
UNION
SELECT 22
UNION
SELECT 23
) Q1
CROSS JOIN
(
SELECT 0 AS MM
UNION
SELECT 5
UNION
SELECT 10
UNION
SELECT 15
UNION
SELECT 20
UNION
SELECT 25
UNION
SELECT 30
UNION
SELECT 35
UNION
SELECT 40
UNION
SELECT 45
UNION
SELECT 50
UNION
SELECT 55
) Q2
ORDER BY
1
results...
Code:
----------------
00:00:00.0000000
00:05:00.0000000
00:10:00.0000000
00:15:00.0000000
00:20:00.0000000
00:25:00.0000000
00:30:00.0000000
00:35:00.0000000
00:40:00.0000000
00:45:00.0000000
00:50:00.0000000
00:55:00.0000000
01:00:00.0000000
01:05:00.0000000
01:10:00.0000000
01:15:00.0000000
01:20:00.0000000
01:25:00.0000000
01:30:00.0000000
01:35:00.0000000
01:40:00.0000000
01:45:00.0000000
01:50:00.0000000
01:55:00.0000000
02:00:00.0000000
02:05:00.0000000
02:10:00.0000000
02:15:00.0000000
02:20:00.0000000
02:25:00.0000000
02:30:00.0000000
02:35:00.0000000
02:40:00.0000000
02:45:00.0000000
02:50:00.0000000
02:55:00.0000000
03:00:00.0000000
03:05:00.0000000
03:10:00.0000000
03:15:00.0000000
03:20:00.0000000
03:25:00.0000000
03:30:00.0000000
03:35:00.0000000
03:40:00.0000000
03:45:00.0000000
03:50:00.0000000
03:55:00.0000000
04:00:00.0000000
04:05:00.0000000
04:10:00.0000000
04:15:00.0000000
04:20:00.0000000
04:25:00.0000000
04:30:00.0000000
04:35:00.0000000
04:40:00.0000000
04:45:00.0000000
04:50:00.0000000
04:55:00.0000000
05:00:00.0000000
05:05:00.0000000
05:10:00.0000000
05:15:00.0000000
05:20:00.0000000
05:25:00.0000000
05:30:00.0000000
05:35:00.0000000
05:40:00.0000000
05:45:00.0000000
05:50:00.0000000
05:55:00.0000000
06:00:00.0000000
06:05:00.0000000
06:10:00.0000000
06:15:00.0000000
06:20:00.0000000
06:25:00.0000000
06:30:00.0000000
06:35:00.0000000
06:40:00.0000000
06:45:00.0000000
06:50:00.0000000
06:55:00.0000000
07:00:00.0000000
07:05:00.0000000
07:10:00.0000000
07:15:00.0000000
07:20:00.0000000
07:25:00.0000000
07:30:00.0000000
07:35:00.0000000
07:40:00.0000000
07:45:00.0000000
07:50:00.0000000
07:55:00.0000000
08:00:00.0000000
08:05:00.0000000
08:10:00.0000000
08:15:00.0000000
08:20:00.0000000
08:25:00.0000000
08:30:00.0000000
08:35:00.0000000
08:40:00.0000000
08:45:00.0000000
08:50:00.0000000
08:55:00.0000000
09:00:00.0000000
09:05:00.0000000
09:10:00.0000000
09:15:00.0000000
09:20:00.0000000
09:25:00.0000000
09:30:00.0000000
09:35:00.0000000
09:40:00.0000000
09:45:00.0000000
09:50:00.0000000
09:55:00.0000000
10:00:00.0000000
10:05:00.0000000
10:10:00.0000000
10:15:00.0000000
10:20:00.0000000
10:25:00.0000000
10:30:00.0000000
10:35:00.0000000
10:40:00.0000000
10:45:00.0000000
10:50:00.0000000
10:55:00.0000000
11:00:00.0000000
11:05:00.0000000
11:10:00.0000000
11:15:00.0000000
11:20:00.0000000
11:25:00.0000000
11:30:00.0000000
11:35:00.0000000
11:40:00.0000000
11:45:00.0000000
11:50:00.0000000
11:55:00.0000000
12:00:00.0000000
12:05:00.0000000
12:10:00.0000000
12:15:00.0000000
12:20:00.0000000
12:25:00.0000000
12:30:00.0000000
12:35:00.0000000
12:40:00.0000000
12:45:00.0000000
12:50:00.0000000
12:55:00.0000000
13:00:00.0000000
13:05:00.0000000
13:10:00.0000000
13:15:00.0000000
13:20:00.0000000
13:25:00.0000000
13:30:00.0000000
13:35:00.0000000
13:40:00.0000000
13:45:00.0000000
13:50:00.0000000
13:55:00.0000000
14:00:00.0000000
14:05:00.0000000
14:10:00.0000000
14:15:00.0000000
14:20:00.0000000
14:25:00.0000000
14:30:00.0000000
14:35:00.0000000
14:40:00.0000000
14:45:00.0000000
14:50:00.0000000
14:55:00.0000000
15:00:00.0000000
15:05:00.0000000
15:10:00.0000000
15:15:00.0000000
15:20:00.0000000
15:25:00.0000000
15:30:00.0000000
15:35:00.0000000
15:40:00.0000000
15:45:00.0000000
15:50:00.0000000
15:55:00.0000000
16:00:00.0000000
16:05:00.0000000
16:10:00.0000000
16:15:00.0000000
16:20:00.0000000
16:25:00.0000000
16:30:00.0000000
16:35:00.0000000
16:40:00.0000000
16:45:00.0000000
16:50:00.0000000
16:55:00.0000000
17:00:00.0000000
17:05:00.0000000
17:10:00.0000000
17:15:00.0000000
17:20:00.0000000
17:25:00.0000000
17:30:00.0000000
17:35:00.0000000
17:40:00.0000000
17:45:00.0000000
17:50:00.0000000
17:55:00.0000000
18:00:00.0000000
18:05:00.0000000
18:10:00.0000000
18:15:00.0000000
18:20:00.0000000
18:25:00.0000000
18:30:00.0000000
18:35:00.0000000
18:40:00.0000000
18:45:00.0000000
18:50:00.0000000
18:55:00.0000000
19:00:00.0000000
19:05:00.0000000
19:10:00.0000000
19:15:00.0000000
19:20:00.0000000
19:25:00.0000000
19:30:00.0000000
19:35:00.0000000
19:40:00.0000000
19:45:00.0000000
19:50:00.0000000
19:55:00.0000000
20:00:00.0000000
20:05:00.0000000
20:10:00.0000000
20:15:00.0000000
20:20:00.0000000
20:25:00.0000000
20:30:00.0000000
20:35:00.0000000
20:40:00.0000000
20:45:00.0000000
20:50:00.0000000
20:55:00.0000000
21:00:00.0000000
21:05:00.0000000
21:10:00.0000000
21:15:00.0000000
21:20:00.0000000
21:25:00.0000000
21:30:00.0000000
21:35:00.0000000
21:40:00.0000000
21:45:00.0000000
21:50:00.0000000
21:55:00.0000000
22:00:00.0000000
22:05:00.0000000
22:10:00.0000000
22:15:00.0000000
22:20:00.0000000
22:25:00.0000000
22:30:00.0000000
22:35:00.0000000
22:40:00.0000000
22:45:00.0000000
22:50:00.0000000
22:55:00.0000000
23:00:00.0000000
23:05:00.0000000
23:10:00.0000000
23:15:00.0000000
23:20:00.0000000
23:25:00.0000000
23:30:00.0000000
23:35:00.0000000
23:40:00.0000000
23:45:00.0000000
23:50:00.0000000
23:55:00.0000000