3

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:

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!

Blazes
  • 4,721
  • 2
  • 22
  • 29

3 Answers3

4

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
Chandu
  • 81,493
  • 19
  • 133
  • 134
  • Perfect! That is an amazing piece of SQL. Thanks for your time. – Blazes Jun 22 '11 at 16:06
  • This solution seems to have a bug. I've changed the data a bit, as shown [here](https://gist.github.com/2205609). Then when I ran your answer's SQL I got incorrect results. This seems to happen since you use `a.[end] - ISNULL(b.start, a.start))` to partition the final results but that is incorrect in the cases in which there are more than a single group, like in my sample. – VitalyB Mar 26 '12 at 14:49
  • Arriving here from google 5 years later, can't see how `OVER(PARTITION BY a.[end] - ISNULL(b.start, a.start))` would work either – OGHaza Sep 15 '16 at 05:03
0

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
Akram Shahda
  • 14,655
  • 4
  • 45
  • 65
  • I don't think that is quite it - it will select only rows where the end of one is the start of another. I need all of the rows, but the consecutive states are combined. – Blazes Jun 22 '11 at 16:03
  • @Blazes: It is easy to down vote, hard to read and bragging is a must !! Have you missed to read the "hint" word in the first line of the answer!! – Akram Shahda Jun 22 '11 at 16:27
0

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
Chandu
  • 81,493
  • 19
  • 133
  • 134
Ovais Khatri
  • 3,201
  • 16
  • 14