Home Forums SQL Server 2008 T-SQL (SS2K8) Consolidate Overlapping Date Periods RE: Consolidate Overlapping Date Periods

  • September 30, 2012 at 9:14 am

    #1543741

    CELKO (9/27/2012)

    The occupancy_end_date has to be NULL-able to show the guests who are still here (basic temporal idiom in SQL)

    That's certainly one way to do it but that typically results in an OR in the JOIN or WHERE clause which can cause performance problems. With that thought in mind, I'll close the interval with a very high date such as 9999-12-30. Notice that I don't use the last date available (9999-12-31) in SQL Server so that I can use the classic and easily SARGable [font="Arial Black"]AND EndDate >= CutoffDate AND EndDate < CutoffDate[/font] where "CutoffDate" is an "open" end of the interval to accomodate when EndDate has a non-midnight time involved. It also allows for standard handling whether a time is involved or not.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)