Run same query for each day across a date range?

[Gooseman]

Hi,

I have a query which works for one day:

SELECT SOME_COL AS something, SOME_COL2 AS something2 FROM myTable
WHERE DATE = '2007-05-11' AND SOME_STAT 1

Returns

something something 2
1 2
3 4

How do I get this to work for a date range (e.g. DATE '2007-05-09')
where I get:

date something something2
2007-05-09 1 2
2007-05-09 3 4
2007-05-10 1 2
2007-05-10 3 4
2007-05-11 1 2
2007-05-11 3 4

Thanks in advance!

[Ed Murphy]

Gooseman wrote:

I have a query which works for one day:

SELECT SOME_COL AS something, SOME_COL2 AS something2 FROM myTable
WHERE DATE = '2007-05-11' AND SOME_STAT 1

Returns

something something 2
1 2
3 4

How do I get this to work for a date range (e.g. DATE '2007-05-09')
where I get:

date something something2
2007-05-09 1 2
2007-05-09 3 4
2007-05-10 1 2
2007-05-10 3 4
2007-05-11 1 2
2007-05-11 3 4

I think the usual method is to create a table containing all dates
that you're likely to ever use, then cross-join to it.

[Plamen Ratchev]

If you just need to select a range of dates regardless of any gaps or
intervals in the range (that is holidays, non-working days, other special
events, etc.), then you can simply use the comparison operators >, <, =, <=,

>=, or BETWEEN, for example:

WHERE DATE '20070508'

WHERE DATE >= '20070509'

WHERE DATE '20070508' AND DATE <='20070511'

WHERE DATE BETWEEN '20070509' AND '20070511'

Note that BETWEEN is inclusive of the start and end expressions.

Also, you can use IN to select a few particular dates:

WHERE DATE IN ('20070509', '20070512', '20070515')

If you DATE column contains values that have time different than midnight,
then you have to be careful about using the correct start/end date to
guarantee correct results.

As stated by Ed, using a calendar table is a great method to handle date
ranges, especially when you have exceptions (gaps) in the range. See one
example of creating and using a calendar table here:
http://sqlserver2000.databases.aspfa...dar-table.html

HTH,

Plamen Ratchev
http://www.SQLStudio.com