In reporting queries it happens often that you need a ruler of some sort for instance a table containing all days between a start and end date. You can generate such tables by using a looping construct but this isn't the strong point of SQL.
Using a single table containing all numbers between 0 and 59 this query can generate a time table with each year,month,day,hour and minute between 1980 and 2039 (this range can be moved or changed).
To generate the ruler table use the first statement. This does require a loop but as this only needs to be generated once this is the easiest way to go.
CREATE TABLE [Ruler] ( [value] int ) DECLARE @i int SET @counter = 0 WHILE @counter < 60 BEGIN INSERT INTO [Ruler] VALUES ( @counter ) SET @counter = @counter + 1 END
The time table is then generated by the following statement:
SELECT 1980 + y.value, m.value, d.value , h.value, mm.value FROM [time] y, [time] m, [time] d, [time] h, [time] mm WHERE m.value BETWEEN 1 and 12 AND d.value BETWEEN 1 and CASE WHEN m.value IN (1,3,5,7,8,10,12) THEN 31 WHEN m.value = 2 THEN CASE WHEN y.value % 400 = 0 THEN 29 WHEN y.value % 100 = 0 then 28 WHEN y.value % 4 = 0 THEN 29 ELSE 28 END ELSE 30 END AND h.value BETWEEN 0 and 23 AND m.value BETWEEN 0 and 59 ORDER BY y.value, m.value, d.value, h.value, mm.value