Post reply

Need help with query

  • June 27, 2014 at 2:17 pm

    #296112

    Hi All

    I have a data set as given below :

    CodeStartDate EndDate UpdatedDate

    1101/01/201401/31/201401/30/2014

    1102/01/201402/28/201402/27/2014

    1103/01/201403/20/201403/20/2104

    1105/01/201405/31/201405/30/2014

    1106/01/201406/30/201406/12/2014

    1107/02/201412/31/202007/05/2014

    22

    22

    22

    Each code has a series of records with startdate,enddate and updateddate . The requirement is to create 2 records out of this ranges. If there is a gap of more than 2 days between the previous end date and the current startdate it indicates a gap.

    So, in the e.g for the 3rd record the enddate is 3/20/2014 and for the 4th record the startdate is 05/01/2014 .

    This indicates a gap of more than 2 days , so the output should be 2 records

    One with the minimum start date 01/01/2014 and enddate as 03/20/2014 with the corresponding updated record which is also 03/20/2014

    There will be another record with startdate as 05/01/2014 and the max end date which is 12/31/2020 with it's corresponding updated date which is 07/05/2014.

    I first used a CTE to order the rows however I have to use sub queries within the CTE which can hamper performance , has somebody got any better approach

  • June 27, 2014 at 2:29 pm

    #1725589

    Quick question, are you on SQL Server 2008R2?

    😎

  • June 27, 2014 at 9:55 pm

    #1725626

    Yes I am on 2008 R2

  • June 27, 2014 at 10:18 pm

    #1725628

    arijit rath (6/27/2014)

    Hi All

    I have a data set as given below :

    CodeStartDate EndDate UpdatedDate

    1101/01/201401/31/201401/30/2014

    1102/01/201402/28/201402/27/2014

    1103/01/201403/20/201403/20/2104

    1105/01/201405/31/201405/30/2014

    1106/01/201406/30/201406/12/2014

    1107/02/201412/31/202007/05/2014

    22

    22

    22

    Each code has a series of records with startdate,enddate and updateddate . The requirement is to create 2 records out of this ranges. If there is a gap of more than 2 days between the previous end date and the current startdate it indicates a gap.

    So, in the e.g for the 3rd record the enddate is 3/20/2014 and for the 4th record the startdate is 05/01/2014 .

    This indicates a gap of more than 2 days , so the output should be 2 records

    One with the minimum start date 01/01/2014 and enddate as 03/20/2014 with the corresponding updated record which is also 03/20/2014

    There will be another record with startdate as 05/01/2014 and the max end date which is 12/31/2020 with it's corresponding updated date which is 07/05/2014.

    I first used a CTE to order the rows however I have to use sub queries within the CTE which can hamper performance , has somebody got any better approach

    With a tweak, the following article explains exactly how to do such a thing.

    http://www.sqlservercentral.com/articles/T-SQL/71550/

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

  • June 28, 2014 at 8:31 am

    #1725660

    Hi Jeff,

    Thanks for the reference, it's a interesting article, here I have 2 dates though so I assume the DataGroup must be created between the startdate and enddate .

    My condition is the difference between the current start date and the previous enddate should be greater than 2 to indicate a gap.

    Can you throw some light as to how I should approach this..

  • June 28, 2014 at 12:01 pm

    #1725675

    My apologies. I'll blame it on a lack of caffeine. I overlooked the fact that you have a start and an end date.

    I had a really good link to a PDF that Itzik Ben-Gan published on this very problem but his company moved servers and apparently never reestablished the link. The next best thing is his article in SQL Magazine (see the final two queries in that article) http://sqlmag.com/blog/solutions-packing-date-and-time-intervals-puzzle. It doesn't, however, cover the "slack" of ignoring gaps of up to two days. That can, however, be fixed by adding a persisted computed column to the table which adds to days to the EndDate and then you subtract the two days from the final output to get the actual EndDate for display purposes.

    I don't have the time just now to write a complete solution for you but the article and my hint above should help. When I can get my head above water (hopefully, sometime before the weekend is over), I'll see if I can provide a coded answer.

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

  • June 28, 2014 at 3:22 pm

    #1725690

    While you're waiting, please see the first link under "Helpful Links" in my signature line below. If you post readily consumable data as prescribed in that article, it would be a big help to me helping you.

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

Viewing 7 posts - 1 through 6 (of 6 total)

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