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.
Value Date 60 1/5/2019 8:00 60 1/5/2019 9:00 60 1/5/2019 10:00 75 1/5/2019 10:30 60 1/5/2019 11:00 40 1/5/2019 12:00 40 1/5/2019 13:00
Desired Output
Value Total Time 60 1/5/2019 8:00 - 10:30 = 2 and a half hours 75 1/5/2019 10:30 - 11:00 = half hour 60 1/5/2019 11:00 - 12:00 = 1 hour 40 1/5/2019 12:00 - 13:00 = 1 hour
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:
select value, min(date), max(date) from (select t.*, row_number() over (order by date) as seqnum, row_number() over (partition by value order by date) as seqnum_v from t ) t group by (seqnum - seqnum_v), value;
But you want the next start, so we need a lead()
as well:
select value, min(date) as startdate, lead(min(date), 1, max(date)) over (order by min(date)) as enddate from (select t.*, row_number() over (order by date) as seqnum, row_number() over (partition by value order by date) as seqnum_v from t ) t group by (seqnum - seqnum_v), value;
And to get the total time:
select value, datediff(minute, min(date), lead(min(date), 1, max(date)) over (order by min(date)) ) as dur_minutes from (select t.*, row_number() over (order by date) as seqnum, row_number() over (partition by value order by date) as seqnum_v from t ) t group by (seqnum - seqnum_v), value;