[Oddish]

I've got a bunch of banners that I want to schedule during different times every year, but I'm having trouble figuring out the sql query for fetching the correct banners. Every banner has a column for "start_date" and one for "end_date" and the rows could look like this:

 start_date  |  end_date
 -------------------------
 2000-01-01  |  NULL
 2000-10-01  |  2000-05-01
 2000-05-01  |  2000-01-01

The year is irrelevant here, since these banners should be recurring during certain periods, every year.

The first row has no end date, so that should always be active
The second row (October 1st - May 1st) should not be active
The third row (May 1st - January 1st) should be active

I don't have the sql knowledge to put this logic into a query though, and I was hoping to get some pointers from the experts here. I've fooled around with CASE statements that involved different sets of conditions depending on whether the start month was larger or smaller than the end month, but I never got anywhere.

Thanks very much in advance for any help you can give me!

[guelphdad]

SELECT
banner
FROM 
yourtable
WHERE
month(current_date) 
BETWEEN
month(startdate) AND month(enddate)

you can of course add the qualifying day clauses in there as well if banners have start/end dates other than the first of the month.

if you don't have the days, then really all you need is a start month and end month in a tinyint column for each.

[r937]

also, don't do what your sample data shows --

2000-10-01 | 2000-05-01

that's not gonna satisfy any BETWEEN range test properly, so you will want to change it to this --

2000-10-01 | 2000-12-01
2000-01-01 | 2000-05-01

can you see why?

[Oddish]

Originally Posted by r937

also, don't do what your sample data shows --

2000-10-01 | 2000-05-01

that's not gonna satisfy any BETWEEN range test properly, so you will want to change it to this --

2000-10-01 | 2000-12-01
2000-01-01 | 2000-05-01

can you see why?

I guess I thought I could get away with just one date range per banner, even though it might overlap a year. I guess I'll have to make some changes.