Post reply

select query for overlap date range!!!

  • October 11, 2004 at 8:41 am

    #162304

    Hi

    can any one please help me in giving the solution with an sql statement which returns the overlapping date ranges

    here is the data in my table

    bookingid    start                 finish

    1001         2004/10/11 08:00      2004/10/11 17:30

    1002         2004/10/11 09:00      2004/10/11 14:30

    1003         2004/10/11 10:00      2004/10/11 18:00

    1004         2004/10/11 11:00      2004/10/11 12:30

    1005         2004/10/11 18:00      2004/10/11 19:30

    i only wanted to return

    1001

    1002

    1003

    1004

    thanks for any help

    mrpro.

  • October 11, 2004 at 10:34 am

    #526211

    Hi

    Can you explain clearly the meaning of overlapped date range?

    Thanks,

    Sridhar!!

  • October 11, 2004 at 12:31 pm

    #526233

    select distinct a.keycolumn

    from tablex a

           join tablex b ON a.startdate between b.startdate and b.enddate

                             OR a.enddate between b.startdate and b.enddate

     

  • October 12, 2004 at 2:50 am

    #526298

    sridhar and john thanks for your response..

    this has solved my problem..

  • November 27, 2012 at 8:43 am

    #1562722

    select distinct a.keycolumn

    from tablex a

    join tablex b ON a.startdate between b.startdate and b.enddate

    OR a.enddate between b.startdate and b.enddate

    would only return cases where the range a.startdate to a.enddate starts or ends between b.startdate and b.enddate.

    if the a range starts before and ends after, what happens?

    or (a.enddate > b.enddate and a.startdate < b.enddate)

    should take care of it.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply