Combine rows in SQL Server

Question

I'm recording time for each operation when it starts and when it ends and how they are different operations me generates multiple rows , which I would like to have consolidated the start time and end if and only if the following operation is recorded within the next minute. If you spend more than one minute it is considered another consolidated row. Example

dbms is sql server 2008 R2

ID | PERSON | START | END | OP |TYPE 1 |2001668 |27/04/2016 22:58|27/04/2016 22:59|5901430|19 2 |2001668 |27/04/2016 23:00|27/04/2016 23:19|5901430|19 3 |2001326 |20/11/2009 04:16|20/11/2009 04:27|5901444|21 4 |2001668 |28/04/2016 11:19|28/04/2016 11:32|5901430|19

I would like the results to look like this:

PERSON | START | END | OP |TYPE 2001668 |27/04/2016 22:58|27/04/2016 11:19|5901430|19 2001326 |20/11/2009 04:16|20/11/2009 04:27|5901444|21 2001668 |28/04/2016 11:19|28/04/2016 11:32|5901430|19

You probably want to use a cursor. If you had a later version of SQL Server you could use `lag()/lead()`. There are likely to be solutions to this problem already in other Stackoverflow questions. — shawnt00, Jun 01 '16 at 18:00
These may help: http://stackoverflow.com/questions/24244659/group-consecutive-rows-of-same-value-using-time-spans http://stackoverflow.com/questions/15161262/merge-adjacent-rows-in-sql — shawnt00, Jun 01 '16 at 18:06
Here is a great place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — Sean Lange, Jun 01 '16 at 18:08
Look but apparently were not the right words for the search . Thanks for the help. — joel lopez, Jun 01 '16 at 18:46

score 0 · Accepted Answer · answered Jun 01 '16 at 20:43

Try this

declare @tb table (username varchar(10),starttime datetime,endtime datetime )
insert into @tb values('0100810','2016-01-04 16:00','2016-01-04 17:00')
insert into @tb values('0100810','2016-01-04 17:01','2016-01-04 17:20')
insert into @tb values('0100820','2016-01-04 18:00','2016-01-04 19:00')

insert into @tb values('0100810','2016-01-04 17:22','2016-01-04 17:30');

--select username,DATEADD(MINUTE,1,starttime) st, starttime,endtime from @tb
WITH StartTimes AS
(
  SELECT DISTINCT username, starttime
  FROM @tb AS S1
  WHERE NOT EXISTS
    (SELECT * FROM @tb AS S2
     WHERE S2.username = S1.username
       AND S2.starttime < S1.starttime
       AND DATEADD(MINUTE,1,S2.endtime) >=S1.starttime)
),
EndTimes AS
(
  SELECT DISTINCT username, endtime
  FROM @tb AS S1
  WHERE NOT EXISTS
    (SELECT * FROM @tb AS S2
     WHERE S2.username = S1.username
       AND S2.endtime > S1.endtime
       AND S2.starttime <= DATEADD(MINUTE,1,S1.endtime))
)
SELECT username, starttime,
  (SELECT MIN(endtime) FROM EndTimes AS E
   WHERE E.username = S.username
     AND DATEADD(MINUTE,1,E.endtime) >= starttime) AS endtime
FROM StartTimes AS S;

Combine rows in SQL Server

1 Answers1