SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Wizard Lats's Avatar
    Join Date
    Jun 2003
    Location
    Melbourne, AU
    Posts
    1,142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Selecting a date range

    I'd like to retrieve some information from a db for a weekly view of user activities - these activities are entered with a start and end date (yyyy-mm-dd). So far, this has worked okay...
    Code:
    SELECT
    	*
    FROM
    	whereis
    WHERE
    	(wi_date_start
    BETWEEN
    	'$start_week'
    AND
    	'$end_week')
    OR
    	(wi_date_end
    BETWEEN
    	'$start_week'
    AND
    	'$end_week')
    ORDER BY
    	wi_userid, wi_date_start
    ...where $start_week & $end_week (yyyy-mm-dd) are generated on the fly.

    As an example for this week, $start_week = '2004-05-24', $end_week = '2004-05-30' and the query extracts all data that either begins or ends in this week.

    The problem though if a user has a start (wi_date_start) date of '2004-05-22' and an end (wi_date_end) date of '2004-06-01', the sql fails.

    Any suggestions/guidance on this would be appreciated.
    Lats...

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    here's the secret for queries involving overlapping date ranges

    first, visualize the possible overlaps

    if $start_week is S and $end_week is E, and if the user's date range is represented by Xs, then here are all the possible overlaps:

    Code:
    ---XXXXX--S---------E---------
    ------XXXXX---------E---------
    ---------XXXXX------E---------
    ----------S--XXXXX--E---------
    ----------S-----XXXXX---------
    ----------S---------XXXXX-----
    ----------S---------E--XXXXX--
    ----------XXXXXXXXXXX---------
    ------XXXXXXXXXXXXXXXXXXX-----
    now try to find the user ranges that you know for sure do not overlap

    the answer: when both wi_date_start and wi_date_end are before S or after E

    anything else is an overlap

    so that's what you search for -- the reverse of no overlap

    Code:
     where NOT ( wi_date_start < '$start_week'
             and wi_date_end   < '$start_week')
       and NOT ( wi_date_start > '$end_week'
             and wi_date_end   > '$end_week')
    easy, eh?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard Lats's Avatar
    Join Date
    Jun 2003
    Location
    Melbourne, AU
    Posts
    1,142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    Quote Originally Posted by r937
    easy, eh?

    Errr, no, but it's slowly sinking in

    Thank you Rudy, that works perfectly.
    Lats...


Bookmarks

Posting Permissions

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