Submitted by st on
There are many examples of user defined table functions generating the series of dates(times) between two specified values. Usually, in OLAP you can see a table filled by the date/time series, too. Such table avoid to generate the values "on the fly".
However, in some cases you may be unable to use an UDF or a table. Fortunately, "pure SQL" method still works.
Suppose, the start and finish dates are specified as entry parameters. The transact SQL code below generates the series "moth by month".
WITH date_limits (min_date_value, max_date_value) AS ( SELECT convert(date, '20100101'), convert(date, '20110201') ) , date_series (date_value, num) AS ( SELECT min_date_value AS date_value, 1 FROM date_limits UNION ALL SELECT dateadd(month, 1, date_series.date_value), num + 1 FROM date_limits INNER JOIN date_series ON date_series.date_value < date_limits.max_date_value ) SELECT date_value, num FROM date_series
Result
date_value num ---------- ----------- 2010-01-01 1 2010-02-01 2 2010-03-01 3 2010-04-01 4 2010-05-01 5 2010-06-01 6 2010-07-01 7 2010-08-01 8 2010-09-01 9 2010-10-01 10 2010-11-01 11 2010-12-01 12 2011-01-01 13 2011-02-01 14
Here is the real world OLAP example: transposing data values with their periods by month.
CREATE TABLE #t ( date_from date, date_to date, value int ) GO INSERT INTO #t VALUES ('20100101', '20100301', 1), ('20100401', '20100501', 2), ('20100501', '20100901', 3) GO SELECT * FROM #t; WITH date_limits (min_date_value, max_date_value) AS ( SELECT min(date_from), max(date_to) FROM #t ) ,date_series (date_value, num) AS ( SELECT min_date_value AS date_value, 1 FROM date_limits UNION ALL SELECT dateadd(month, 1, date_series.date_value), num + 1 FROM date_limits INNER JOIN date_series ON date_series.date_value < date_limits.max_date_value ) SELECT ds.date_value, t.value FROM date_series ds INNER JOIN #t t ON ds.date_value BETWEEN t.date_from AND t.date_to GO DROP TABLE #t GO
Result
date_from date_to value ---------- ---------- ----------- 2010-01-01 2010-03-01 1 2010-04-01 2010-05-01 2 2010-05-01 2010-09-01 3 date_value value ---------- ----------- 2010-01-01 1 2010-02-01 1 2010-03-01 1 2010-04-01 2 2010-05-01 2 2010-05-01 3 2010-06-01 3 2010-07-01 3 2010-08-01 3 2010-09-01 3