Many times developers need some data concerning calendar dates of the year
in a usable format; for example, in a table, so you can fetch data, search data,
create joins, and do other useful things needed in processing application business
logic. The common problem is when you need to have the dates following one day
after another in a table format.
What can you do to solve this issue? It is not good to store the dates in
the table because it uses space and generation mechanisms are needed to create
the dates table. One can try to create such table on-the-fly by creating a loop
and inserting dates in the table of dates:
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
|
This code works, but performance is not good. It is not efficient to have
a loop and make queries through the loop. In T-SQL, you can enhance performance
by using a single query that simulates a loop:
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 code returns a table of 40, with ordered numbers from 0 to 39. If we
develop this query a bit, we can achieve the desired result with calendar dates:
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
|
The calendar table is generated, but let's not stop at this point. Since we
need this query for development, let's create a user-defined function out of
it and use it in a single line of code:
create function F_TBL_CALENDAR
(@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 cal_date
from
(
select
@p_start_date +
n3.num * 100 +
n2.num * 10 +
n1.num as cal_date
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.
Now, to get a dates calendar table of 2004, you simply use the following code:
|
select top 366 * from dbo.F_TBL_CALENDAR(2004)
|
In conclusion, the right use of a single query simulating loops can enhance
performance, sometimes dramatically. Also, it is useful to create tables on-the-fly
when you have to deal with iterating data such as calendar dates, numbers, etc.
You can visit my blog at http://gensystem.europe.webmatrixhosting.net/al/.