Tyler Lillemo Tyler Lillemo - 6 months ago 17
SQL Question

how to group consecutive rows?

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. 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

Answer

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 -- First determine alarm intervals.
AS 
(
    SELECT       EventStart.Ev_Comment AS StartDateTime 
                ,MIN(COALESCE (EventEnd.Ev_Comment, EventStart.Ev_Comment)) AS EndDateTime
    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'
    GROUP BY     EventStart.Ev_Comment
),
AlarmsByTimeBucket -- Get the alarms by time intervals.
AS
(
    SELECT      AlarmTimeBuckets.StartDateTime
               ,AlarmTimeBuckets.EndDateTime 
               ,Alarm.Ev_Custom1 AS Alarm
               ,(
                 -- Case statement determine if this event is a new event or simply part of a root alarm that got split
                 CASE
                    WHEN LAG(AlarmTimeBuckets.EndDateTime, 1, NULL) OVER (PARTITION BY Alarm.Ev_Custom1 ORDER BY AlarmTimeBuckets.StartDateTime) = AlarmTimeBuckets.StartDateTime THEN 0
                    ELSE 1
                 END
                ) AS IsNewEvent
    FROM       A Alarm 
    INNER JOIN AlarmTimeBuckets  ON Alarm.Ev_Comment = AlarmTimeBuckets.StartDateTime
    WHERE     (Alarm.Ev_Custom1 <> 'Alarms Scanned')
)
,
AlarmsByGroupingID -- Generate a grouping ID that can be used to group related events (split events) together.
AS
(
    SELECT   StartDateTime
            ,EndDateTime
            ,Alarm
            ,SUM(IsNewEvent) OVER (ORDER BY Alarm, StartDateTime) AS GroupingID -- If this is a new event then IsNewEvent will be 1 and the SUM will increase the Grouping ID 
                                                                                -- else the value will be 0 and the grouping id will be the same which will allow us to group the two (or more) events together.
    FROM    AlarmsByTimeBucket
)
SELECT      MIN(StartDateTime) AS StartDateTime
            ,MAX(EndDateTime) AS EndDateTime
            ,MAX(Alarm) AS Alarm
FROM        AlarmsByGroupingID
GROUP BY    GroupingID          -- Group related alarm events together.
ORDER BY    StartDateTime
Comments