SQL Count Unacknowledged Alarms

Question

I'm looking to count the number of alarms that became active in a time range but were not acknowledged and the number of alarms that were not cleared. There are three types of events that are all stored in the same table: alarm activation, acknowledgment, and clearing.

The table I am querying looks kind of like this.

ID	Alarm Name	Event Type	Is Active	Is Acknowledged	Date Time
1	Alarm 1	Acknowledged	True	True	12/22/2020 06:00:00
2	Alarm 1	Cleared	False	True	12/22/2020 06:30:00
3	Alarm 2	Active	True	False	12/22/2020 07:00:00
4	Alarm 3	Active	True	False	12/22/2020 07:30:00
5	Alarm 2	Acknowledged	True	True	12/22/2020 08:00:00
6	Alarm 2	Connection Lost	True	True	12/22/2020 08:30:00
7	Alarm 2	Connection Regained	True	True	12/22/2020 09:00:00
8	Alarm 3	Cleared	False	False	12/22/2020 09:30:00
9	Alarm 2	Cleared	False	True	12/22/2020 10:00:00
10	Alarm 3	Active	True	False	12/22/2020 10:30:00

The results from this table would be 2 alarms not acknowledged and 1 alarm not cleared. The first two rows should be ignored because the initial Active event is not in the time range. Alarm 2 becomes active, acknowledged and then cleared. Alarm 3 becomes active and is cleared before being acknowledged. Alarm 3 becomes active again but there are no acknowledgment or clearing events.

I had to do something similar with a different database where every row event for the same alarm had an id. In that case I joined the acknowledgement events and the clearing events to the active events using that id and then counted where the joins were null. But here, I don't have that alarm id. I wrote a query that would add a unique ID. Here it is:

SELECT AlarmName,
SUM(CASE WHEN EventType = 'Active' THEN 1 ELSE 0 END) OVER (PARTITION BY AlarmName ORDER BY DateTime) as InstanceID
FROM AllEvent
ORDER BY AlarmName, InstanceID

Any ideas?

Hey, for the DDL/DML, do you mean to include a statement that would create the table? — Benjamin Sperl, Dec 28 '20 at 13:49
Yes, create a temp table, insert some sample data... that way its ready for someone to copy and paste out and being testing. — Dale K, Dec 28 '20 at 19:15

score 0 · Answer 1 · answered Dec 22 '20 at 22:36

For a fixed expected sequence of "Active" > "Acknowledged" > "Cleared", you can use lead():

select 
    sum(case when lead_event1 = 'Acknowledged' then 0 else 1 end) as cnt_not_ack,
    sum(case when lead_event2 = 'Cleared' then 0 else 1 end) as cnt_not_cleared
from (
    select t.*,
        lead(event_type, 1) over(partition by alarm_name order by date_time) as lead_event1
        lead(event_type, 2) over(partition by alarm_name order by date_time) as lead_event1
    from mytable t
) t
where event_type = 'Active'

For each row, lead() retrieves the next and next next event type for the same alarm number). We can then filter on "Active" events only, and check if the following events are a ack and a clearance. The final step is aggregation.

That's pretty clever! Unfortunately, an alarm can be acknowledged after it is cleared and there are also some other types of events such as if the connection to the alarm is lost. — Benjamin Sperl, Dec 28 '20 at 13:31

SQL Count Unacknowledged Alarms

1 Answers1