Post reply

Overlapping date ranges

  • September 8, 2004 at 7:47 am

    #60422

    If I have a table with a begin_date & end_date field, does anyone know a good method to check for overlapping date ranges?

    Thanks!

    Kurt 

  • September 8, 2004 at 7:53 am

    #522125

    Please post DDL and sample data for this table. Are you searching an entire table for overlapping dates? Or a subset based on a key? Or...?

    --
    Adam Machanic
    whoisactive

  • September 9, 2004 at 6:46 am

    #522286

    Select * From [Table] Where Begin_Date Between '01/01/2004' And '01/15/2004'??  Not sure exactly what you're looking for

  • September 9, 2004 at 8:49 am

    #522338

    Try:

    select * from tablename a inner join tablename b on a.RecID <> b.RecID and ((a.begin_date <= b.end_date and a.end_date >= b.end_date) or (a.end_date >= b.begin_date and a.begin_date <= b.begin_date))

    assuming that 'tablename' is your table and 'RecID' is the record id field

    Regards

    Peter

     

  • September 9, 2004 at 9:00 am

    #522342

    declare @DateRanges table ( BeginDt datetime, EndDt datetime )

    INSERT INTO @DateRanges

          SELECT Begin_Date, End_Date FROM MyTable

     

    SELECT A.* FROM MyTable A

          WHERE 1 < ( SELECT COUNT(*) FROM @DateRanges B

                                  B.BeginDt <= A.End_Date AND B.EndDt >= A.Begin_Date )

    The above can be done without the temp table but will likely not perform as well. I haven't tried running it so the bottom most where clause might not quite be correct, but I think that this should at least be the general idea of what you are after.

  • September 9, 2004 at 2:41 pm

    #522392

    thanks!

Viewing 6 posts - 1 through 6 (of 6 total)

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