Date 'look ahead' and group

  • Hi

    Is there an easy way to get start and end dates that are consecutive and show them as one result. So instead of...

    1 July 2019 Holiday

    2 July 2019 Holiday

    3 July 2019 Holiday

    4 July 2019 Holiday

    5 July 2019 Business

    6 July 2019 Business


    1-4 July 2019 Holiday

    5-6 July 2019 Business

    I'm sure there's a terminology for this but can't think of it. Clues would be preferable than an actual answer.


  • Subtract a ROW_NUMBER() from the date to get the group for consecutive dates.  I'll let you work out the details of the ROW_NUMBER() since you said you preferred clues to actual answers.


    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA



    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • Jason Selburg wrote:


    PIVOT is too restrictive for what the OP is trying to accomplish, because it only allows one aggregate function, and you need a MIN() and a MAX().


    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • How should the following sequence be presented?


    30 December 2019 Business

    31 December 2019 Holiday

    1 January 2019 Holiday

    2 January 2019 Business


    Code for TallyGenerator

Viewing 5 posts - 1 through 5 (of 5 total)

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