how to group consecutive rows?

Question

So, I have a table with rows like this:

Ev_Message       Ev_Comment             EV_Custom1           Ev_Time_Ms     
-------------------------------------------------------------------------------------
Machine 1 Alarm  5/23/2016 11:02:00 AM  Alarms Scanned       25              
Machine 1 Alarm  5/23/2016 11:00:00 AM  Alarms Scanned       686 
Machine 1 Alarm  5/23/2016 11:00:00 AM  Light curtain        537
Machine 1 Alarm  5/23/2016 11:00:00 AM  Guard door open      346 
Machine 1 Alarm  5/23/2016 11:00:00 AM  No control voltage   135 
Machine 1 Alarm  5/23/2016 10:38:34 AM  Alarms Scanned       269
Machine 1 Alarm  5/23/2016 10:38:29 AM  Alarms Scanned       378
Machine 1 Alarm  5/23/2016 10:38:29 AM  Guard door open      156
Machine 1 Alarm  5/23/2016 10:38:25 AM  Alarms Scanned       654
Not an Alarm     5/23/2016 10:38:25 AM  Not an Alarm         467     
Machine 1 Alarm  5/23/2016 10:38:25 AM  Guard door open      234
Machine 1 Alarm  5/23/2016 10:38:25 AM  No control voltage   67
Machine 1 Alarm  5/23/2016 10:38:23 AM  Alarms Scanned       124
Machine 1 Alarm  5/23/2016 10:38:23 AM  No control voltage   100

An "Alarms Scanned" row is added every time the alarms are scanned for which is every time an alarm is triggered or cleared. Any alarms will add a row with a specific Ev_Custom1. the first column Ev_Message, holds a machine ID that lets me separate out alarms from different machines. (don't you love the arbitrary column names?) There are over nine hundred unique alarm messages.

What I want my query to return is something like this:

Alarm Message       Alarm Start Time       Alarm Stop Time  
----------------------------------------------------------------  
No control voltage  5/23/2016 10:38:23 AM  5/23/2016 10:38:29 AM  
Guard door open     5/23/2016 10:38:25 AM  5/23/2016 10:38:34 AM  
No control voltage  5/23/2016 11:00:00 AM  5/23/2016 11:02:00 AM  
Guard door open     5/23/2016 11:00:00 AM  5/23/2016 11:02:00 AM  
Light curtain       5/23/2016 11:00:00 AM  5/23/2016 11:02:00 AM

This would be a query filtered between two dates. I have some ability to change the data going into the table but with 900 alarms my freedom is limited.

With some help, my current query is this:

WITH T AS (
    SELECT     s.Ev_Comment AS start_time,
               MIN(COALESCE (e.Ev_Comment, s.Ev_Comment)) AS end_time
    FROM       A AS s
    INNER JOIN A AS e
            ON s.Ev_Comment < e.Ev_Comment
           AND s.Ev_Custom1 = 'Alarms Scanned'
           AND e.Ev_Custom1 = 'Alarms Scanned'
    GROUP BY   s.Ev_Comment)
SELECT     T_1.start_time,
           T_1.end_time,
           A.Ev_Custom1
FROM       A
INNER JOIN T AS T_1
        ON A.Ev_Comment LIKE T_1.start_time
WHERE      (A.Ev_Custom1 <> 'Alarms Scanned')

I still have one problem. if an alarm lasts for longer than one period like the 'Guard Door Open' from 10:38:25 to 10:38:34 then it will show up in two separate lines like so:

start_time             end_time               EV_Custom1   
---------------------  ---------------------  -------------
5/23/2016 10:38:25 AM  5/23/2016 10:38:29 AM  Guard door open
5/23/2016 10:38:29 AM  5/23/2016 10:38:34 AM  Guard door open

When ideally what I want is:

start_time             end_time               EV_Custom1   
---------------------  ---------------------  -------------
5/23/2016 10:38:25 AM  5/23/2016 10:38:34 AM  Guard door open

I think I need to group by ((Ev_custom1) and (when end_time = start_time)) (pardon my pseudo-code) but I don't know enough about the syntax required for this.

Here is an SQLFiddle

its the timestamp of the "Alarm scanned" row that does not have the alarm with the same time stamp. indicating that during that scan the alarm was not found. — z_temp_string, May 26 '16 at 19:55

Edmond Quinton · Accepted Answer · 2016-05-27T14:44:02.610

If I understand the posted problem correctly, then your CTE effectively determines time buckets (or intervals) for all of your alarms. Your final select clause joins the actual alarm information with your alarm intervals. Part of your problem is your alarming system will continue to log “Alarms Scanned” entries if your alarm remains active for prolonged periods (I assume longer than your alarm scan cycle) which effectively causes active alarms to be split. If you have SQL Server 2012 or higher, then it is relatively easy to determine if the alarm event got split. You simply need to check if the end time of an alarm is equal to the start time of the next alarm of the same alarm type. You can achieve this with the use of the LAG windowing function in 2012.
The next step is to generate an ID that you can group your alarm by so that you can combine your split events. This is achieved via the SUM OVER clause. The following example shows how this can be achieved:

;WITH AlarmTimeBuckets
AS 
(
    SELECT       EventStart.Ev_Comment AS StartDateTime 
                ,MIN(COALESCE (EventEnd.Ev_Comment, EventStart.Ev_Comment)) AS EndDateTime
                ,EventStart.Ev_Message As Machine
    FROM         A EventStart 
    INNER JOIN   A EventEnd ON EventStart.Ev_Comment < EventEnd.Ev_Comment AND EventStart.Ev_Custom1 = 'Alarms Scanned' AND EventEnd.Ev_Custom1 = 'Alarms Scanned' AND EventStart.Ev_Message = EventEnd.Ev_Message
    GROUP BY     EventStart.Ev_Message, EventStart.Ev_Comment
),
AlarmsByTimeBucket
AS
(
    SELECT      AlarmTimeBuckets.Machine
               ,AlarmTimeBuckets.StartDateTime
               ,AlarmTimeBuckets.EndDateTime 
               ,Alarm.Ev_Custom1 AS Alarm
               ,(
                 CASE
                    WHEN LAG(AlarmTimeBuckets.EndDateTime, 1, NULL) OVER (PARTITION BY Alarm.Ev_Custom1,Alarm.Ev_Message ORDER BY AlarmTimeBuckets.StartDateTime) = AlarmTimeBuckets.StartDateTime THEN 0
                    ELSE 1
                 END
                ) AS IsNewEvent
    FROM       A Alarm 
    INNER JOIN AlarmTimeBuckets  ON Alarm.Ev_Message = AlarmTimeBuckets.Machine AND  Alarm.Ev_Comment = AlarmTimeBuckets.StartDateTime
    WHERE     (Alarm.Ev_Custom1 <> 'Alarms Scanned')
)
,
AlarmsByGroupingID
AS
(
    SELECT   Machine
            ,StartDateTime
            ,EndDateTime
            ,Alarm
            ,SUM(IsNewEvent) OVER (ORDER BY Machine, Alarm, StartDateTime) AS GroupingID
    FROM    AlarmsByTimeBucket
)
SELECT       MAX(Machine) AS Machine
            ,MIN(StartDateTime) AS StartDateTime
            ,MAX(EndDateTime) AS EndDateTime
            ,MAX(Alarm) AS Alarm
FROM        AlarmsByGroupingID
GROUP BY    GroupingID
ORDER BY    StartDateTime

turns out i am using SQL Server 2012. i am querying through Microsoft Visual Studio 2010 using SQL Server Reporting Services. i copied your answer in verbatim and it gave me exactly what i wanted. — z_temp_string, May 26 '16 at 20:08
i was wrong. you failed to take into account the first column Ev_Message. this column holds a machine ID that lets me separate out alarms from different machines. (don't you love the arbitrary column names?) your query doesn't look at that column. ill see if i can tweek it myself. — z_temp_string, May 27 '16 at 14:15
@TylerLillemo please update your original post to reflect the importance of EV_Message since this is the first time you mentioned it. — Edmond Quinton, May 27 '16 at 14:23
thank you i was able to fix it i believe by adding a WHERE statement in between the first `inner join` and `group by` — z_temp_string, May 27 '16 at 14:33
@TylerLillemo you will also need to update the windowing calls form OVER (PARTITION BY Alarm.Ev_Message to OVER (PARTITION BY Alarm.Ev_Custom1,Alarm.Ev_Message. I have update my answer to reflect this change. — Edmond Quinton, May 27 '16 at 14:46
Thank you for your help. I have updated my query to match and it is working very well. it worked for me without that step because the few that i tested happened to have slightly differing alarm text between the two machines. — z_temp_string, May 27 '16 at 14:51

msheikh25 · Answer 2 · 2016-05-26T20:04:46.483

I updated your sqlfiddle link as well with the updates below. In your final result set, you need to set a row_number and join back to it on EV_CUSTOM1, START_TIME = END_TIME (as you suspected) and also row number = row number+1. This is how you can determine if two events are in the same period. It would be a bit more simple if you were on Sql Server 2012+ where you have the LAG/LEAD functions available as @EdmondQuinton pointed out in his answer.

WITH T AS (SELECT  s.Ev_Comment AS start_time, MIN(COALESCE (e.Ev_Comment, s.Ev_Comment)) AS end_time           
           FROM A AS s 
           INNER JOIN A AS e 
           ON s.Ev_Comment < e.Ev_Comment 
           AND s.Ev_Custom1 = 'Alarms Scanned' 
           AND e.Ev_Custom1 = 'Alarms Scanned'
           GROUP BY s.Ev_Comment
          ),

T2 AS(SELECT T_1.start_time, T_1.end_time, A.Ev_Custom1,
             ROW_NUMBER() OVER (PARTITION BY EV_CUSTOM1 ORDER BY T_1.START_TIME) RN
      FROM  A 
      INNER JOIN
      T AS T_1 
      ON A.Ev_Comment LIKE T_1.start_time
      WHERE (A.Ev_Custom1 <> 'Alarms Scanned')
      )

select 
  coalesce(b.START_TIME, a.START_TIME) START_TIME, 
  max(a.END_TIME) END_TIME, 
  a.EV_CUSTOM1
from T2 a
left outer join T2 b
on a.EV_CUSTOM1 = b.EV_CUSTOM1
and a.START_TIME = b.END_TIME
and a.RN = b.RN+1
group by coalesce(b.START_TIME, a.START_TIME), 
         a.EV_CUSTOM1

thank you for your answer. it does work as well. i had to add a WHERE statement in between the first `inner join` and `group by` similar to @EdmondQuinton 's post. my apologies for not making it clear that i needed that. — z_temp_string, May 27 '16 at 14:40
for some reason i am getting two separate lines for alarms lasting longer than one day. @EdmondQuinton 's solution does not have this issue. — z_temp_string, May 27 '16 at 15:33
I'm not able to replicate that. From your sample data, if I change the final alarm from `5/23/2016 11:02:00 AM` to `5/24/2016 11:02:00 AM` it still seems to work fine. I can take a look if you have additional data but it sounds like you have a working solution already. — msheikh25, May 27 '16 at 15:48

how to group consecutive rows?

2 Answers2

Linked