Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003
    Posts
    43

    Unanswered: Recurring time frames

    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:

    Code:
     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!

  2. #2
    Join Date
    Mar 2004
    Posts
    480
    Code:
    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.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Mar 2003
    Posts
    43
    Quote 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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •