I have some queries that used to be from the vision demo project (maybe they still are being used)
Alarms by count
With TopItems AS ( SELECT displaypath , COUNT(*) cnt , ROW_Number() OVER (ORDER BY count(id) DESC) AS NUM FROM alarm_events a WHERE eventtime BETWEEN '2023-07-09 09:40:24.253' AND '2023-07-12 09:40:24.253' AND Priority > 0 AND eventtype = 0 AND a.displaypath != '' GROUP BY displaypath) SELECT displaypath, COALESCE(cnt,0) cnt FROM TopItems WHERE NUM <= 5 Union ALL SELECT 'Other', COALESCE(SUM(cnt),0) From TopItems WHERE NUM > 5 ORDER BY cnt DESC, displaypath ASC
Alarms by Duration
SELECT TOP 5 a.displaypath, SUM(DATEDIFF(MINUTE, a.eventtime, COALESCE(c.eventtime, CURRENT_TIMESTAMP))) duration FROM alarm_events a LEFT JOIN alarm_events c ON c.eventid = a.eventid AND c.eventtype = 1 WHERE a.eventtime BETWEEN '2023-10-15 09:40:24.253' AND '2023-10-18 09:40:24.253' AND a.priority > 0 AND a.eventtype = 0 AND a.displaypath != '' GROUP BY a.displaypath ORDER BY duration DESC, a.displaypath ASC
I also have some associated data PartNumber and LotNumber as strings on my alarms and I would like to be able to filter this query down to use these too. I am tempted to give up on the SQL solution and manipulate this data in python to get what I want. I can see that the id coulmn in alarm_event_data is going to have to be matched up with the alarm_events id column but I was wondering if anyone has done these modifications to these queries before and feels like sharing..