If you ever had the pleasure to run queries over an access log I bet you had a desperate need for a date sequence to map query results to.
There are plenty of pages out there discussing the best way to create and populate number tables. After reading this great post on stack overflow I came up with a slight variation of one of the solutions mentioned there, like so:
1 2 3 4 5 6 7 8 9 10 11 12 13 |
DECLARE @fromDate DATETIME = '2014-01-01' DECLARE @toDate DATETIME = '2014-09-30' SELECT datum = DATEADD(DAY, number - 1, @FromDate) FROM ( SELECT number = CAST(number AS INT) FROM ( SELECT number = ROW_NUMBER() OVER (ORDER BY v1.number) FROM master..spt_values AS v1 CROSS JOIN (SELECT number FROM master..spt_values) AS v2 ) AS vu WHERE number BETWEEN 1 AND DATEDIFF(DAY, @fromDate, DATEADD(DAY, 1, @toDate)) ) AS u |
This returns 273 records with dates from Jan, 1 2014 to Sep, 30 2014.
The advantages of this solution are:
- no need to recur to a pre-generated numbers table
- is simple and concise and can easily be used as a subquery
- upper limit of number generator depends only on the size of master..spt_values – table (which contains 2047 records at minimum, so the cartesian product gives at least a sequence up to 4.194.304). If you want to know more about spt_values, see this post here.
Of course, it is tedious to repeat yourself over and over again. Better you wrap it in user-defined functions.
First, the number generator:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -- f_generatedNumbers -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -- returns a Table with records from @lowerBound to @upperBound -- generated on-the-fly -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - USE <YOUR_DATABASE_NAME_HERE> GO IF OBJECT_ID(N'f_generatedNumbers') IS NOT NULL DROP FUNCTION f_generatedNumbers GO CREATE FUNCTION f_generatedNumbers(@lowerBound INT, @upperBound INT) RETURNS @tbl TABLE (number INT) AS BEGIN INSERT @tbl (number) SELECT number FROM ( SELECT number = CAST(number - 1 AS INT) FROM ( SELECT number = ROW_NUMBER() OVER (ORDER BY v1.number) FROM master..spt_values AS v1 CROSS JOIN (SELECT number FROM master..spt_values) AS v2 ) AS vu WHERE number BETWEEN @lowerBound + 1 AND @upperBound + 1 ) AS u RETURN END GO |
Then the date sequence generator, which is building on the number generator:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -- f_DateSequenceGenerator_with_generated_numbers -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - USE <YOUR_DATABASE_NAME_HERE> GO IF OBJECT_ID (N'f_DateSequenceGenerator_gen') IS NOT NULL DROP FUNCTION f_DateSequenceGenerator_gen GO CREATE FUNCTION f_DateSequenceGenerator_gen (@datepart varchar(12), @fromDate DATETIME, @toDate DATETIME) RETURNS @reTable TABLE (datum DATETIME) AS BEGIN IF UPPER(@datepart) = 'MONTH' OR @datepart = 'mm' OR @datepart = 'm' BEGIN INSERT @reTable (datum) SELECT datum FROM ( SELECT datum = DATEFROMPARTS(yearNumber, monthNumber, 1) FROM ( SELECT DISTINCT yearNumber = y.number, monthNumber FROM f_generatedNumbers(YEAR(@fromDate), YEAR(@toDate)) AS y CROSS JOIN (SELECT DISTINCT monthNumber = number FROM f_generatedNumbers(1,12)) AS m ) AS u1 ) AS u WHERE datum BETWEEN @FromDate AND @toDate RETURN END IF UPPER(@datepart) = 'DAY' OR @datepart = 'dd' OR @datepart = 'd' BEGIN INSERT @reTable (datum) SELECT datum = DATEADD(DAY, number - 1, @FromDate) FROM f_generatedNumbers(1, DATEDIFF(DAY, @fromDate, DATEADD(DAY, 1, @toDate))) END RETURN END GO |
This function has two parts – enclosed in if – statements – generating sequences for months and for days.
Now, it’s very easy to get a time period in days with:
1 2 |
SELECT datum FROM f_DateSequenceGenerator_gen('DAY', '1753-01-01', '9999-12-30') |
at least within the date range available for a SQL Server DATETIME type going from Jan, 1 1753 to Dec, 31 9999 (my solution is missing the last day due to the DATEADD operation which would result in an overflow error).
If you want to dig deeper, read:
- Aaron Bertrands article Generate a set or sequence without loops is sort of a reference to the topic. He also has a look on performance issues.
- This answer on stack overflow presents an almost comprehensive overview of various methods to create and populate a numbers table.