Post reply

Overlapping records

  • March 22, 2011 at 1:33 pm

    #248533

    Hi,

    I am trying to find out records with overlapping dates. ONLY WANT RECORDS THAT HAVE OVERLAPPING DATES.

    For e.g.

    badge site extension start date end date

    123123 1234 4321 1/1/2011 3/1/2011

    123123 1234 4321 2/1/2011 null

    overlapping - not good - 2 records valid from 2/1/11 through 3/1/11

    badge site extension start date end date

    321321 4321 1234 1/1/2011 3/1/2011

    321321 4321 1234 2/1/2011 3/15/2011

    overlapping - not good - 2 records valid from 2/1/11 through 3/1/11

    badge site extension start date end date

    765432 4567 5432 1/1/2011 3/1/2011

    765432 4567 5432 3/1/2011 null

    sequential records - no problem

    badge site extension start date end date

    234567 7890 6543 1/1/2011 3/1/2011

    234567 7890 6543 3/1/2011 3/15/2011

    sequential records - no problem

    Is this something easy to do or do I have to join to the same table?

    Thanks in advance.

  • March 22, 2011 at 2:30 pm

    #1302178

    This should get you close;

    select b.Badge,

    b.Site,

    b.Extension,

    b.start_date

    from BadgeSiteExtension a inner join BadgeSiteExtension b

    on a.Badge = b.Badge

    and a.Site = b.Site

    and a.Extension = b.Extension

    and b.start_date > a.start_date

    and b.start_date < a.end_date

    So, yes a join back to itself would be the way to go. Depending on the size of the table you might need to do something different to get it to perform efficiently.

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • March 22, 2011 at 2:39 pm

    #1302181

    Thank You!:-)

  • March 22, 2011 at 2:45 pm

    #1302184

    You're welcome. One consideration which may not be valid but if you have two entries with null end_date's that previous will not work. The following will (included data that I used for testing);

    insert into BadgeSiteExtension values (123125, 1235, 4325, '1/1/2011', null)

    insert into BadgeSiteExtension values (123125, 1235, 4325, '2/1/2011', null)

    select b.Badge,

    b.Site,

    b.Extension,

    b.start_date

    from BadgeSiteExtension a inner join BadgeSiteExtension b

    on a.Badge = b.Badge

    and a.Site = b.Site

    and a.Extension = b.Extension

    and b.start_date > a.start_date

    and b.start_date < ISNULL(a.end_date, GETDATE())

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • March 22, 2011 at 10:57 pm

    #1302270

    For each Badge and Site, will there be only 2 rows ?

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

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