Combine rows when the end time of one is the start time of another

I am having difficulty writing a query. I need to combine rows of time-consecutive states into a single state. For example, given the data:

start                    end                      state   seconds
2011-04-21 08:13:30.000  2011-04-21 08:18:00.000  STATE1  270
2011-04-21 08:18:00.000  2011-04-21 08:22:30.000  STATE1  270
2011-04-21 08:22:30.000  2011-04-21 08:26:26.000  STATE1  236
2011-04-21 08:26:26.000  2011-04-21 08:26:47.000  STATE2  21
2011-04-21 08:26:47.000  2011-04-21 08:27:30.000  STATE3  43
2011-04-21 08:27:30.000  2011-04-21 08:28:20.000  STATE1  50
2011-04-21 08:40:30.000  2011-04-21 08:41:00.000  STATE1  30

I need to combine rows only when row2.state = row1.state AND row2.start = row1.end and come up with an overall start and end time of the state. The result should be:

  • How to use Switch in SQL Server
  • Does SQL Server CACHES Query Results?
  • Query to copy rows from sql server to another sql server
  • Server Role to execute SQL Server Agent Jobs in SQL SERVER 2008
  • Code to Insert Image into SQL Server
  • How to export from SQL Server to XML
  • start                    end                      state   seconds
    2011-04-21 08:13:30.000  2011-04-21 08:26:26.000  STATE1  776
    2011-04-21 08:26:26.000  2011-04-21 08:26:47.000  STATE2  21
    2011-04-21 08:26:47.000  2011-04-21 08:27:30.000  STATE3  43
    2011-04-21 08:27:30.000  2011-04-21 08:28:20.000  STATE1  50
    2011-04-21 08:40:30.000  2011-04-21 08:41:00.000  STATE1  30
    

    Here is the example SQL:

    CREATE TABLE Data (
        ID int IDENTITY(1,1) not null,
        Date DateTime not null,
        State nvarchar(40) not null,
        Seconds int not null,
        PRIMARY KEY(ID)
    );
    INSERT INTO Data(Date,State,Seconds) VALUES('2011-04-21 08:13:30.000', 'STATE1', 270)
    INSERT INTO Data(Date,State,Seconds) VALUES('2011-04-21 08:18:00.000', 'STATE1', 270)
    INSERT INTO Data(Date,State,Seconds) VALUES('2011-04-21 08:22:30.000', 'STATE1', 236)
    INSERT INTO Data(Date,State,Seconds) VALUES('2011-04-21 08:26:26.000', 'STATE2', 21)
    INSERT INTO Data(Date,State,Seconds) VALUES('2011-04-21 08:26:47.000', 'STATE3', 43)
    INSERT INTO Data(Date,State,Seconds) VALUES('2011-04-21 08:27:30.000', 'STATE1', 50)
    INSERT INTO Data(Date,State,Seconds) VALUES('2011-04-21 08:40:30.000', 'STATE1', 30)
    
    SELECT Date as 'start', DATEADD(s,Seconds,Date) as 'end', State, Seconds FROM Data
    

    Thanks in advance!

    3 Solutions collect form web for “Combine rows when the end time of one is the start time of another”

    Try this(>= SQL Server 2005):

    WITH qry AS
    (
        SELECT  a.*
                        ,ROW_NUMBER() OVER (ORDER BY [start]) rn
        FROM    (SELECT Date as 'start', DATEADD(s,Seconds,Date) as 'end', State, Seconds FROM Data) a
    )
    SELECT  DISTINCT MIN(a.start) OVER(PARTITION BY a.State, a.[end] - ISNULL(b.start, a.start)) ,
                    MAX(a.[end] ) OVER(PARTITION BY a.State, a.[end] - ISNULL(b.start, a.start)) ,
                    a.state
                    ,SUM(a.Seconds) OVER(PARTITION BY a.State, a.[end] - ISNULL(b.start, a.start)) 
    
      FROM qry a LEFT JOIN qry b
            ON a.rn + 1 = b.rn
       AND a.[end] = b.start
    

    Use the following as a hint:

    SELECT
        T1.StartDate,
        T2.EndDate
    FROM
        MyTable T1
    INNER JOIN
        MyTable T2 
        ON T1.Status = T2.Status AND
        T1.EndDate = T2.StartDate
    

    try this:

    SELECT d1.Date as 'start', d2.Date as 'end', d1.State,SUM(d1.Seconds) FROM Data d1,Data d2
    where 
    convert(varchar(5),d1.Date,8) = convert(varchar(5),DATEADD(s,d2.Seconds,d2.Date),8)
    AND 
    d1.State = d2.State
    group by d1.Date,d1.State,d2.Date
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.