0

I can't get this to work despite this helper which is very similar.

Combine rows when the end time of one is the start time of another (Oracle)

Example data

groupId startTime               endTime
-------------------------------------------------------
1022    2015-09-14 06:30:00.000 2015-09-14 13:45:00.000
1022    2015-09-14 11:20:00.000 2015-09-14 11:50:00.000
1477    2015-09-14 09:46:00.000 2015-09-14 16:13:00.000
1477    2015-09-14 13:40:00.000 2015-09-14 14:10:00.000
2037    2015-09-14 09:43:00.000 2015-09-14 12:00:00.000
2037    2015-09-14 12:00:00.000 2015-09-14 19:02:00.000
2037    2015-09-14 14:00:00.000 2015-09-14 14:30:00.000

The output I want.

groupId startTime               endTime
-------------------------------------------------------
1022    2015-09-14 06:30:00.000 2015-09-14 13:45:00.000
1022    2015-09-14 11:20:00.000 2015-09-14 11:50:00.000
1477    2015-09-14 09:46:00.000 2015-09-14 16:13:00.000
1477    2015-09-14 13:40:00.000 2015-09-14 14:10:00.000
2037    2015-09-14 09:43:00.000 2015-09-14 19:02:00.000
2037    2015-09-14 14:00:00.000 2015-09-14 14:30:00.000

And the example sql.

CREATE TABLE data 
(
    groupId int not null,
    startTime datetime not null,
    endTime datetime not null
)

INSERT INTO Data (groupId, startTime, endTime) 
VALUES (1022, '2015-09-14 06:30:00.000', '2015-09-14 13:45:00.000')

INSERT INTO Data (groupId, startTime, endTime) 
VALUES (1022, '2015-09-14 11:20:00.000', '2015-09-14 11:50:00.000')

INSERT INTO Data (groupId, startTime, endTime) 
VALUES (1477, '2015-09-14 09:46:00.000', '2015-09-14 16:13:00.000')

INSERT INTO Data (groupId, startTime, endTime) 
VALUES (1477, '2015-09-14 13:40:00.000', '2015-09-14 14:10:00.000')

INSERT INTO Data (groupId, startTime, endTime) 
VALUES (2037, '2015-09-14 09:43:00.000',    '2015-09-14 12:00:00.000')

INSERT INTO Data (groupId, startTime, endTime) 
VALUES (2037, '2015-09-14 12:00:00.000', '2015-09-14 19:02:00.000')

INSERT INTO Data (groupId, startTime, endTime) 
VALUES (2037, '2015-09-14 14:00:00.000', '2015-09-14 14:30:00.000')

Thank you.

Community
  • 1
  • 1
Don
  • 111
  • 2
  • 8
  • Do you ever get the case there there is more then one row that continue an existing row? another row with this data 2037 2015-09-14 12:00 2015-09-14 20:00 – Paul Spain Sep 28 '15 at 23:47
  • Not yet. But if I did, I'd want the output to be 2037 2015-09-14 09:43:00.000 2015-09-14 20:00:00.000 where the endTime is updated to 2015-09-14 20:00:00.000. – Don Sep 28 '15 at 23:58

1 Answers1

0

Maybe a better way but when in doubt CTE....

WITH CTES as 
(select * from #data)

select c.groupid,c.starttime,ISNULL(d.endtime,c.endtime)
FROM CTES c
left join CTES d
on c.starttime = d.endtime
and c.groupid = d.groupid
where c.starttime != ISNULL(d.endtime,c.endtime)
Holmes IV
  • 1,673
  • 2
  • 23
  • 47