Skip to content
Advertisement

Combine date ranges for rows with same value

Data Looks Like Below – my desired output is that when the next entry in the day is the same value those times are combined to get a total time. Once a value changes, take the date of the first entry (of that value) to the new value so that we get the combined time that value lasted.

Desired Output

Advertisement

Answer

This is a gaps and islands problem. For this version, I think the difference of row numbers is the simplest solution. So, this almost solves your problem:

But you want the next start, so we need a lead() as well:

And to get the total time:

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement