joel lopez joel lopez - 6 months ago 23
SQL Question

Combine rows in SQL Server

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

Answer

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;