Recently I was working on a data warehouse project and was tasked to generate a date dimension table.
Now some of the available options for me to achieve the same are listed out as below:
- Using the loops in SQL to iterate and populate dates
- Using the date wizard in SSAS (multidimensional)
- Copy the table data from other pre-populated table
But I stumbled upon a technique which caught my interest to populate the date dimension via the following SQL query
DECLARE @StartDate DATE = '20000101', @NumberOfYears INT = 15; DECLARE @CutoffDate DATE = DATEADD(YEAR, @NumberOfYears, @StartDate); SELECT d FROM ( SELECT d = DATEADD(DAY, rn - 1, @StartDate) FROM ( SELECT TOP (DATEDIFF(DAY, @StartDate, @CutoffDate)) rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id]) FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2 -- on my system this would support > 5 million days ORDER BY s1.[object_id] ) AS x ) AS y;
And from the above, I found it more smarter and optimal way of achieving the same, hence I set out to share.
Hope it helps…