Post reply

Group "Overlapping" Dates

  • October 30, 2010 at 2:30 pm

    #242466

    I hope folks don't mind me adding some problems to this forum and then solving them with an article. This one is asked over and over and over... I've got this one and have just submitted an "SQL Spackle" article on it.

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

  • October 30, 2010 at 10:34 pm

    #1244591

    Jeff - what do you mean by "overlapping" dates, and how does this differ from determining gaps/islands?

    (If it's different, we probably ought to add one for each of those.)

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • October 31, 2010 at 12:05 am

    #1244594

    I think Jeff means extending date ranges.

    If you have three date ranges

    20100101-20100630, 20100515-20100930 and 20100928-20101010,

    the end result is 20100101-20101010.

    N 56°04'39.16"
    E 12°55'05.25"

  • October 31, 2010 at 10:02 am

    #1244632

    WayneS (10/30/2010)

    Jeff - what do you mean by "overlapping" dates, and how does this differ from determining gaps/islands?

    (If it's different, we probably ought to add one for each of those.)

    It's the same as determining "islands" for dates whether or not there are duplicate dates or not. As you and I both know, it can be a bit different than finding gaps/islands in integer data. It can also be the same but most folks usually go for a different tact when it's integer based.

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

  • October 31, 2010 at 10:07 am

    #1244633

    SwePeso (10/31/2010)

    I think Jeff means extending date ranges.

    If you have three date ranges

    20100101-20100630, 20100515-20100930 and 20100928-20101010,

    the end result is 20100101-20101010.

    That would be a separate article I was going to work on but I'll be happy to let someone else tackle it if they'd like. The name of the article I just submitted is actually "Group "Islands" of Contiguous Dates (SQL Spackle) " because I realized later on just exactly what Peter brought up above. We have to be careful to not confuse finding islands in a single column with finding islands in overlapping date ranges.

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

  • October 31, 2010 at 11:40 pm

    #1244710

    Jeff Moden (10/31/2010)

    SwePeso (10/31/2010)

    I think Jeff means extending date ranges.

    If you have three date ranges

    20100101-20100630, 20100515-20100930 and 20100928-20101010,

    the end result is 20100101-20101010.

    That would be a separate article I was going to work on but I'll be happy to let someone else tackle it if they'd like. The name of the article I just submitted is actually "Group "Islands" of Contiguous Dates (SQL Spackle) " because I realized later on just exactly what Peter brought up above. We have to be careful to not confuse finding islands in a single column with finding islands in overlapping date ranges.

    So did either of you decide to pick up and write about the problem that Peter posted in the quote above?

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

  • November 1, 2010 at 1:51 pm

    #1245052

    No need to add it if you'll write it. I'm more looking for a place to keep track of holes that I find in the article base.

  • November 1, 2010 at 2:12 pm

    #1245069

    Steve Jones - SSC Editor (11/1/2010)

    No need to add it if you'll write it. I'm more looking for a place to keep track of holes that I find in the article base.

    But, if it's added, then others that think up one of these quickies will know it's already been handled.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • January 3, 2011 at 8:43 am

    #1268500

    Closed as this one is complete.

Viewing 9 posts - 1 through 8 (of 8 total)

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