T-SQL: Generate Sequences in a Loop Query
Sometimes there is a need to get calendar dates sequences of the year in useable format, for example table, so one can fetch data, search data, create joins and do other useful things, needed in processing application logic.
The common problem is when you need to get the dates sequences in a table format.
So, what can we do. Sometimes it is not efficient to store dates in the table prepared beforehand, because it uses space and some generation mechanism needed to create the dates sequences table beforehand. So we shall try to create such table on the fly. One can say - "big deal, I will create a loop and insert dates in the table of dates." Let's see some example:
DECLARE @gencalendar TABLE (cal_date DATETIME PRIMARY KEY)
DECLARE @p_date SMALLDATETIME
SET @p_date = '20040101'
WHILE @date <= '20041231'
BEGIN
INSERT INTO @gencalendar(cal_date)
VALUES(@p_date)
SET @date = dateadd(d, 1, @p_date)
END
SELECT *
FROM @gencalendar
It works, but performance is not good. You know, it is not productive in SQL to make a loop and make many queries through the loop.
In T-SQL we can boost performance in this case by using a single query, that simulates a loop - a Loop Query. For example, consider the following query:
SELECT n3.num * 10 + n2.num mynumber
FROM (SELECT 0 AS num UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9) n2
,(SELECT 0 AS num UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3) n3
ORDER BY 1
This loop query returns a table of 40 ordered numbers from 0 to 39.
If we develop this idea a bit - we can achieve our desired result with sequence of calendar dates, using sequence loop queries. Let's see, what we get:
SELECT TOP 366 *
FROM (SELECT CONVERT(SMALLDATETIME, '20040101') +
n3.num * 100 +
n2.num * 10 +
n1.num AS CalendarDate
FROM (SELECT 0 AS num union ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9) n1
,(SELECT 0 AS num UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9) n2
,(SELECT 0 AS num UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3) n3
) GenCalendar
ORDER BY 1
We got our calendar dates sequence table. We will not stop at this point. Since we need this query for development, let's create UDF (user defined function) out of it and then use it in a single line of code.
CREATE FUNCTION F_GetCalendarTable
(@p_year smallint)
RETURNS @tbl TABLE (cal_date SMALLDATETIME)
AS
BEGIN
-- 2 --
DECLARE @p_start_date SMALLDATETIME
SET @p_start_date = CONVERT(SMALLDATETIME,
CONVERT(VARCHAR(4),@p_year) + '0101')
-- 3 --
INSERT INTO @tbl
SELECT CalendarDate
FROM (SELECT @p_start_date +
n3.num * 100 +
n2.num * 10 +
n1.num AS CalendarDate
FROM (SELECT 0 AS num union ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9) n1
,(SELECT 0 AS num UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9) n2
,(SELECT 0 AS num UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3) n3
) GenCalendar
ORDER BY 1
RETURN
END
This function gets a year as an argument and returns a table of 400 dates. Let's say we need dates calendar table of 2004:
SELECT TOP 366 * FROM dbo.GetCalendarTable(2004)
In conclusion I can say the following: the right use of a single loop query simulating sequences can boost performance (sometimes dramatically). Also it is useful to create tables on the fly when you have to deal with iterating, sequencing data such as calendar dates, sequence numbers, etc.
This article was published on sqljunkies.com
Wednesday, March 24, 2004 3:52 AM
