|
My favourite solution for this type of problem uses
an integers table. Here's the code to create it:
create table integers (i integer)
insert into integers (i) values (0)
insert into integers (i) values (1)
insert into integers (i) values (2)
insert into integers (i) values (3)
insert into integers (i) values (4)
insert into integers (i) values (5)
insert into integers (i) values (6)
insert into integers (i) values (7)
insert into integers (i) values (8)
insert into integers (i) values (9)
Those 10 rows are sufficient to create, with one or more cross joins,
whatever range of integers you need. For example, you could generate
the numbers 0 through 999 with a triple cross join like this:
select 100*hundreds.i + 10*tens.i + units.i as iii
from integers hundreds
, integers tens
, integers units
Why iii? Because that reminds me that it's a number
up to 999. Note that the query uses table list syntax without
join conditions, thus joining every row from all three "copies" of the
integers table with every other row. This table list syntax for a cross
join will work in any database. In effect, it is the same query as the
following one using explicit JOIN syntax:
select 100*hundreds.i + 10*tens.i + units.i as iii
from integers hundreds
cross
join integers tens
cross
join integers units
The words hundreds, tens, and units
are table aliases, required to distinguish the rows from the
three different "copies" of the table that are present in the query. Usually
I write h, t, and u, to make typing easier.
Even more convenient is a view for this query:
create view integers999 (iii)
as
select 100*h.i+10*t.i+u.i
from integers h
cross
join integers t
cross
join integers u
Now you can use the iii column in an expression to
produce all the dates for this year, using a WHERE clause to restrict
the upper end of the range:
select dateadd(d,iii,'2004-01-01') as thedate
from integers999
where iii <= 365
Simple, eh? Note that there are 366 days in 2004,
so '2004-01-01' + 0 days will be the first date,
and '2004-01-01' + 365 days will be the last.
|
