Home Forums Article Discussions Article Discussions by Author Discuss Content Posted by Jeff Moden Group Islands of Contiguous Dates (SQL Spackle) RE: Group Islands of Contiguous Dates (SQL Spackle)

  • June 27, 2011 at 6:45 pm

    #1344919

    quickdraw (6/27/2011)

    Oh, that row_number trick is so beautiful it makes me want to cry!

    If you'd like to see a similar "Row_Number Trick" on steroids 😉 to solve the problem when the dates and times aren't contiguous and are truly overlapping, check out Itzik's article on the subject. The man's use of simple mathematics is something to behold. Here's the link:

    http://www.solidq.com/sqj/Pages/2011-March-Issue/Packing-Intervals.aspx

    That site does require a membership to read the full article just as SQLServerCentral does. And, like SQLServerCentral, membership is free and safe and they only need your email address. They don't sell your email address nor give it to "interested parties" unless you allow them to by not unchecking some of the "agreement" boxes.

    --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)