Date 'look ahead' and group

Question

Date 'look ahead' and group

lanky_doodle

SSCrazy

Points: 2378
More actions
April 5, 2019 at 9:13 pm

#2886685

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!

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

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

drew.allen SSC Guru Points: 76944 More actions · Answer 1

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: 24641 More actions · Answer 2

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: 76944 More actions · Answer 3

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: 110206 More actions · Answer 4

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