Current table:
Name State Start Time
A Start 17/01 2pm
A update 17/01 4pm
B Start 18/01 3pm
B Start 18/01 5pm
C Start 18/01 6pm
B Start 19/01 2pm
Output I'm looking for:
Name StartTime End Time
A 17/01 2pm 17/01 4pm
B 17/01 4pm 18/01 5pm
C 18/01 5pm 18/01 6pm
B 18/01 6pm 19/01 2pm
Can any one advise?
according to your comments you should use window functions LAG or LEAD. take a look to the following sample code:
LAG: Accesses data from a previous row in the same result set.
LEAD: Accesses data from a subsequent row in the same result set.
with
cte
as
(
select
[Name] ,
[State] ,
lag(StartTime) over(order by [Name], [State], StartTime) as StartTime ,
StartTime as EndTime
from
dbo.Test
)
select
[Name] ,
[State] ,
min(StartTime) as StartTime ,
max(EndTime) as EndTime
from
cte
group by
[Name] ,
[State]