Run same query for each day across a date range?

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!

Re: Run same query for each day across a date range?

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.

Re: Run same query for each day across a date range?

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.aspfaq.com/why-should-i-consi der-using-an-auxiliary-calendar-table.html

HTH,

Plamen Ratchev
http://www.SQLStudio.com