Итак, у меня есть таблица с такими строками:
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
Строка «Сканированные сигналы тревоги» добавляется каждый раз, когда сканируются сигналы тревоги, то есть каждый раз, когда сигнал тревоги срабатывает или сбрасывается. Любые тревоги будут добавлять строку с определенным Ev_Custom1. первый столбец Ev_Message содержит идентификатор машины, что позволяет отделить тревоги от разных машин. (вам не нравятся произвольные имена столбцов?) Существует более девятисот уникальных аварийных сообщений.
То, что я хочу, чтобы мой запрос возвращал, выглядит примерно так:
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
Это будет запрос, отфильтрованный между двумя датами. У меня есть некоторая возможность изменить данные, поступающие в таблицу, но с 900 тревогами моя свобода ограничена.
С некоторой помощью мой текущий запрос таков:
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')
У меня еще есть одна проблема. если тревога длится более одного периода, например, «Открыта дверь охраны» с 10:38:25 до 10:38:34, тогда она будет отображаться в двух отдельных строках, например:
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
Когда в идеале я хочу:
start_time end_time EV_Custom1
--------------------- --------------------- -------------
5/23/2016 10:38:25 AM 5/23/2016 10:38:34 AM Guard door open
Я думаю, что мне нужно group by ((Ev_custom1) and (when end_time = start_time))
(простите за мой псевдокод), но я недостаточно знаю синтаксис, необходимый для этого.