CODEWITHSUNDEEP
sqlsql-serversql-server-2008
z_temp_string
z_temp_string

Reputation: 63

How to group rows separated by a specific row in SQL

So, i have a table with rows like so

Edit: added 2 columns
Second edit: removed one column cause im lazy and dont want to add it.

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

A "Alarms Scanned" row is added every time the alarms are scanned for. Any alarms will add a row with a specific Ev_Custom1. Alarms are scanned for anytime the state of an alarm changes. 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

Now, I know when the alarms turned off because I get an "Alarm Scanned" message with no alarms at the same time or the next scan has other alarms but not the same as before. But I don't know how to tell SQL that. This would be a query filtered between two dates. I think I could get something close with a Group by and choosing the Min(Ev_Comment) As StartTime and Max(Ev_Comment) As AlmostEndTime with something to grab the timestamp out of the following "Alarms Scanned" row as EndTime but this wont help if I have the same alarm go off and on multiple times within the time frame. I have some ability to change the data going into the table but with 900 alarms my freedom is limited.

Second Edit:

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')

This is the query i am currently using based off of James K. Lowden's Very helpful answer. my query designer tweeked the syntax a bit and i changed the final 'FROM ... ON ...' statement to filter out the line where the start time of the alarm matches the end time of the previous period. 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:29 AM  5/23/2016 10:38:34 AM  Guard door open
5/23/2016 10:38:25 AM  5/23/2016 10:38:29 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

Upvotes: 2

Views: 146

Answers (1)

James K. Lowden
James K. Lowden

Reputation: 7837

There's actually not quite enough information in the data you presented to produce the results you want. I can get you partway there, though.

The alarm periods are found by joining the table to itself. 

 select   s.Ev_Comment as start_time
           , min(coalesce(e.Ev_Comment, s.Ev_Comment))
             as end_time
    from A as s 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

    ;
start_time             end_time             
---------------------  ---------------------
5/23/2016 10:38:23 AM  5/23/2016 10:38:25 AM
5/23/2016 10:38:25 AM  5/23/2016 10:38:29 AM
5/23/2016 10:38:29 AM  5/23/2016 10:38:34 AM
5/23/2016 10:38:34 AM  5/23/2016 11:00:00 AM
5/23/2016 11:00:00 AM  5/23/2016 11:02:00 AM

To find things that happen during those intervals, join that result to the table, where the even is between start and end times. I'm just going to show the Light curtain event:

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 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 start_time, end_time, EV_Custom1
from A join T
on Ev_Comment between start_time and end_time
where EV_Custom1 <> 'Alarms Scanned'
;

start_time             end_time               EV_Custom1   
---------------------  ---------------------  -------------
5/23/2016 10:38:34 AM  5/23/2016 11:00:00 AM  Light curtain
5/23/2016 11:00:00 AM  5/23/2016 11:02:00 AM  Light curtain
...

And here's that event by itself, for comparison. 

select * from A where EV_Custom1 = 'Light curtain'
Ev_Message       Ev_Comment             EV_Custom1   
---------------  ---------------------  -------------
Machine 1 Alarm  5/23/2016 11:00:00 AM  Light curtain

The problem is that once you have the alarm periods, it's impossible to uniquely identify the period that item belongs to, because its time is equal to the end of one period and the beginning of another.

To resolve that, you need either finer timestamps, or another column of row numbers. (You can't depend on the order the rows are displayed on the page, because rows in a table have no order.) I think you'll be able to work out the details from there.

Upvotes: 1

Related Questions