Home Forums SQL Server 2014 Development - SQL Server 2014 How to extract overlapping date ranges from a table of date ranges RE: How to extract overlapping date ranges from a table of date ranges

  • February 12, 2016 at 8:34 pm

    #1858552

    stig.benning (2/12/2016)

    Thanks a lot. Works like a dream. I mark this as the solution because I can apply it directly to my subscription table. Now I just have to go learn SQL 🙂 Any suggestions as to where I can do that on the level present here? Thank you in advance.

    The SQL is the easy part. The math that Itzik used is brilliant (and surprisingly simple, which is part of why it's so brilliant) and is the key to this. Break the code up and run each cascading CTE in turn and see the results. The SQL will come auto-magically when you do that.

    As for the test data, you can find out how to do such a thing in the following two articles...

    http://www.sqlservercentral.com/articles/Data+Generation/87901/

    http://www.sqlservercentral.com/articles/Test+Data/88964/

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