MS Access 2010, как рассчитать время между двумя записями - одинаковый идентификатор, разное время

У меня есть список записей с AlarmID, System, State и Raised ON.

Одно событие представлено двумя записями с одним и тем же AlarmID, первая с состоянием On, вторая с State Off и соответствующим временем.

Это мой запрос:

SELECT *
FROM [Alarmi_On-Off]
WHERE [Alarmi_On-Off].[Alarm ID] IN (SELECT [Alarmi_On-Off].[Alarm ID] 
                                     FROM [Alarmi_On-Off]
                                     GROUP BY [Alarmi_On-Off].[Alarm ID] 
                                     HAVING COUNT([Alarmi_On-Off].[Alarm ID]) > 1);

и результат:

+---------+---------+-------+------------------+
| AlarmID | System  | State |    Raised On     |
+---------+---------+-------+------------------+
|    1001 | System1 | On    | 16.08.2016 14:23 |
|    1001 | System1 | Off   | 16.08.2016 16:17 |
|    1002 | System2 | On    | 27.06.2016 12:14 |
|    1002 | System2 | Off   | 27.06.2016 12:24 |
|    1002 | System2 | On    | 14.07.2016 03:37 |
|    1002 | System2 | Off   | 14.07.2016 03:38 |
+---------+---------+-------+------------------+

Я хочу создать запрос, который вычисляет разницу во времени между состояниями «Включено» и «Выключено» для каждой системы, имея в виду, чтобы исключить все системы с менее чем двумя событиями (в приведенном выше примере исключить AlarmID 1001). .."

В этом запросе вместо двух записей у меня была бы одна запись с системой и расчетным временем.

Любая помощь приветствуется.


sql time vba ms-access calc
person Vedran    schedule 31.08.2016    source источник

Ответы (2)


arrow_upward
0
arrow_downward

Синтаксис (и, возможно, производительность) в Access будет очень плохим для этого, но что-то вроде этого должно работать:

SELECT AlarmID,System,
    (SELECT TOP 1 [Raised On] FROM [Alarmi_On-Off] 
     WHERE A.AlarmID = AlarmID 
     AND A.[Raised On] < [Raised On] 
     AND State = 'Off' ORDER BY [Raised On]) - [Raised On] AS Duration 
FROM [Alarmi_On-Off] AS A 
WHERE State = 'On' 
ORDER BY AlarmID,[Raised On]

Обратите внимание, что значение Duration будет Double, представляющим дни и дробные дни. Вы можете либо преобразовать его в Date, либо извлечь части даты и времени по мере необходимости с помощью функций даты и времени Access, то есть:

SELECT AlarmID,System,HOUR(Duration) AS Hours, MINUTE(Duration) AS Minutes FROM
(
SELECT AlarmID,System,
    (SELECT TOP 1 [Raised On] FROM [Alarmi_On-Off] 
     WHERE A.AlarmID = AlarmID 
     AND A.[Raised On] < [Raised On] 
     AND State = 'Off' ORDER BY [Raised On]) - [Raised On] AS Duration 
FROM [Alarmi_On-Off] AS A 
WHERE State = 'On' 
ORDER BY AlarmID,[Raised On]
) T

Результаты с вашими образцами данных:

AlarmID   System     Hours   Minutes
1001      System1    1       54
1002      System2    0       10
1002      System2    0       1
person Comintern    schedule 31.08.2016

arrow_upward
0
arrow_downward

Вы можете использовать следующий запрос, чтобы найти разницу во времени между состояниями «Вкл.» и «Выкл.»:

SELECT
    start_log.AlarmID,
    start_log.Rasied_On AS start_time,
    end_log.Rasied_On AS end_time,
    DATEDIFF(MINUTE, start_log.Rasied_On, end_log.Rasied_On) AS TimeInMin
FROM
    [Alarmi_On-Off] AS start_log
INNER JOIN
    [Alarmi_On-Off] AS end_log ON (
            start_log.AlarmID = end_log.AlarmID AND start_log.Rasied_On < end_log.Rasied_On)
WHERE start_log.State = 'On'
AND end_log.State = 'Off'
GROUP BY start_log.AlarmID, start_log.Rasied_On, end_log.Rasied_On

Вывод Я имею в виду разницу во времени в минутах: Разница во времени

person AT-2017    schedule 31.08.2016
comment
Я получаю декартово произведение времени начала и окончания, если на каждый AlarmID приходится более одной пары как в Access, так и в SQL Server. - person Comintern; 31.08.2016
comment
Большое спасибо всем, я попробую на следующей неделе, когда вернусь в свой офис. - person Vedran; 07.09.2016