Date 'look ahead' and group

  • lanky_doodle

    SSCrazy

    Points: 2116

    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

    ...this:

    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.

    Thanks!

  • drew.allen

    SSC Guru

    Points: 76492

    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.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Jason Selburg

    SSC-Insane

    Points: 24560

    PIVOT

    ______________________________________________________________________

    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
  • drew.allen

    SSC Guru

    Points: 76492

    Jason Selburg wrote:

    PIVOT

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

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Sergiy

    SSC Guru

    Points: 109668

    How should the following sequence be presented?

     

    30 December 2019 Business

    31 December 2019 Holiday

    1 January 2019 Holiday

    2 January 2019 Business

     

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

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