2

I want to calculate the difference between a status change, however the same state can have multiple records, see example below.

create table #DeviceMessages
(
    DoorName nvarchar(50),
    [StatusDoor] nvarchar(50),
    EventEndDate DatetIme
)

INSERT INTO #DeviceMessages
  (DoorName, [StatusDoor], EventEndDate)
VALUES
('Door 001', 'OPEN', '2017-03-23 16:50:42.143'),
('Door 001', 'CLOSED','2017-03-23 16:54:15.813'),
('Door 001', 'CLOSED','2017-03-23 17:53:12.757'),
('Door 001', 'OPEN', '2017-03-23 18:22:37.560'),
('Door 001', 'CLOSED','2017-03-23 18:28:01.197'),
('Door 001', 'CLOSED','2017-03-23 18:28:01.250'),
('Door 001', 'OPEN', '2017-03-23 18:29:40.593'),
('Door 001', 'OPEN', '2017-03-23 18:29:40.600'),
('Door 001', 'CLOSED','2017-03-23 18:34:53.827'),
('Door 001', 'CLOSED','2017-03-23 18:34:53.843'),
('Door 001', 'OPEN', '2017-03-23 18:42:56.463'),
('Door 001', 'OPEN', '2017-03-23 18:42:56.470'),
('Door 001', 'CLOSED','2017-03-23 18:46:32.550');
GO

It goes well when there are multiple CLOSED records, but it doesn't work when there are multiple OPEN records. At that moment I want the earliest OPEN record, compared to the nearest CLOSED record. I use the following statement:

    WITH DoorActivityWPairedTimes
AS
(SELECT [StatusDoor]
, EventEndDate 
, DeviceDoorOpen = DoorName
, DeviceDoorClosed = LEAD(DoorName, 1) OVER(ORDER BY DoorName, EventEndDate)
, DoorOpen = EventEndDate
, DoorClosed = LEAD(EventEndDate, 1) OVER(ORDER BY DoorName, EventEndDate)
, DoorOpenActivityDate = CAST(EventEndDate AS DATE)
, DoorDichtActivityDate = CAST(LEAD(EventEndDate, 1) OVER(ORDER BY DoorName, EventEndDate) AS DATE)
FROM #DeviceMessages)

SELECT DeviceDoorOpen Device
, EventEndDate
, DoorOpen
, DoorClosed 
, DATEDIFF(second, DoorOpen, DoorClosed) DurationOpenSec
FROM DoorActivityWPairedTimes
WHERE [StatusDoor] = 'OPEN'
  AND DoorOpen Is Not Null
  AND DeviceDoorOpen = DeviceDoorClosed
  AND DoorDichtActivityDate = DoorOpenActivityDate
  order by EventEndDate asc

The result of the query above:

+----------+-------------------------+-------------------------+-------------+
|  Device  |        DoorOpen         |        DoorClosed       | Dur.OpenSec |
+----------+-------------------------+-------------------------+-------------+
| Door 001 | 2017-03-23 16:50:42.143 | 2017-03-23 16:54:15.813 |         213 |
| Door 001 | 2017-03-23 18:22:37.560 | 2017-03-23 18:28:01.197 |         324 |
| Door 001 | 2017-03-23 18:29:40.593 | 2017-03-23 18:29:40.600 |           0 |
| Door 001 | 2017-03-23 18:29:40.600 | 2017-03-23 18:34:53.827 |         313 |
| Door 001 | 2017-03-23 18:42:56.463 | 2017-03-23 18:42:56.470 |           0 |
| Door 001 | 2017-03-23 18:42:56.470 | 2017-03-23 18:46:32.550 |         216 |
+----------+-------------------------+-------------------------+-------------+

WANTED RESULT

+----------+-------------------------+-------------------------+-------------+
|  Device  |        DoorOpen         |        DoorClosed       | Dur.OpenSec |
+----------+-------------------------+-------------------------+-------------+
| Door 001 | 2017-03-23 16:50:42.143 | 2017-03-23 16:54:15.813 |         213 |
| Door 001 | 2017-03-23 18:22:37.560 | 2017-03-23 18:28:01.197 |         324 |
| Door 001 | 2017-03-23 18:29:40.593 | 2017-03-23 18:34:53.827 |         313 |
| Door 001 | 2017-03-23 18:42:56.463 | 2017-03-23 18:46:32.550 |         216 |
+----------+-------------------------+-------------------------+-------------+

Can some helpme to make a query that makes the wanted result?

2

IMHO this question can be solved using a classic GROUPING AND WINDOWS solution.

Reset point

According to your question:

I want the earliest OPEN record, compared to the nearest CLOSED record.

Two or more contiguous CLOSED records, require two different groups.

I've a set a reset point when:

  • Last StatusDoor IS NULL (First record)
  • Last StatusDoor is CLOSED and current StatusDoor is CLOSED. (Nearest CLOSED record).
  • Current StatusDoor is OPEN and last StatusDoor is CLOSED (New Open/Closed event).

In addition, I've added a MinMaxOrder just to ensure OPEN events can be fetched before CLOSED events.

WITH ResetPoint AS
(
    SELECT DoorName, [StatusDoor], EventEndDate,
           CASE WHEN ([StatusDoor] = 'CLOSED'
                     AND  COALESCE(LAG(StatusDoor) OVER (PARTITION BY DoorName ORDER BY EventEndDate), 'CLOSED') = 'CLOSED')
                     OR
                     ([StatusDoor] = 'OPEN'
                     AND  COALESCE(LAG(StatusDoor) OVER (PARTITION BY DoorName ORDER BY EventEndDate), 'CLOSED') = 'CLOSED') 
                THEN 1 END IsReset
    FROM   #DeviceMessages
)
   , SetGroup AS
   (
        SELECT DoorName, [StatusDoor], EventEndDate,
               CASE WHEN StatusDoor = 'OPEN' THEN 0 ELSE 1 END MinMaxOrder,
               COUNT(IsReset) OVER (PARTITION BY DoorName ORDER BY [EventEndDate]) grp
        FROM   ResetPoint
    )
SELECT * FROM SetGroup;
GO
DoorName | StatusDoor | EventEndDate        | MinMaxOrder | grp
:------- | :--------- | :------------------ | ----------: | --:
Door 001 | OPEN       | 23/03/2017 16:50:42 |           0 |   1
Door 001 | CLOSED     | 23/03/2017 16:54:15 |           1 |   1
Door 001 | CLOSED     | 23/03/2017 17:53:12 |           1 |   2
Door 001 | OPEN       | 23/03/2017 18:22:37 |           0 |   3
Door 001 | CLOSED     | 23/03/2017 18:28:01 |           1 |   3
Door 001 | CLOSED     | 23/03/2017 18:28:01 |           1 |   4
Door 001 | OPEN       | 23/03/2017 18:29:40 |           0 |   5
Door 001 | OPEN       | 23/03/2017 18:29:40 |           0 |   5
Door 001 | CLOSED     | 23/03/2017 18:34:53 |           1 |   5
Door 001 | CLOSED     | 23/03/2017 18:34:53 |           1 |   6
Door 001 | OPEN       | 23/03/2017 18:42:56 |           0 |   7
Door 001 | OPEN       | 23/03/2017 18:42:56 |           0 |   7
Door 001 | CLOSED     | 23/03/2017 18:46:32 |           1 |   7

As far as each CLOSED event produces a new group, I can get MIN and MAX event of each group, and return only those groups with more than one record.

--= Set a reset point each time the door opens and last event is a closed event
--
WITH ResetPoint AS
(
    SELECT DoorName, [StatusDoor], EventEndDate,
           CASE WHEN ([StatusDoor] = 'CLOSED'
                     AND  COALESCE(LAG(StatusDoor) OVER (PARTITION BY DoorName ORDER BY EventEndDate), 'CLOSED') = 'CLOSED')
                     OR
                     ([StatusDoor] = 'OPEN'
                     AND  COALESCE(LAG(StatusDoor) OVER (PARTITION BY DoorName ORDER BY EventEndDate), 'CLOSED') = 'CLOSED') 
                THEN 1 END IsReset
    FROM   #DeviceMessages
)
   --= Set a group for each reset point, and establish specific order as 0='OPEN', 1='CLOSED'
   --
   , SetGroup AS
   (
        SELECT DoorName, [StatusDoor], EventEndDate,
               CASE WHEN StatusDoor = 'OPEN' THEN 0 ELSE 1 END MinMaxOrder,
               COUNT(IsReset) OVER (PARTITION BY DoorName ORDER BY [EventEndDate]) grp
        FROM   ResetPoint
    )
    --= Get max and min event date avoiding single CLOSED events.
    --
    SELECT   DoorName,
             MIN(EventEndDate) DoorOpen,      
             MAX(EventEndDate) DoorClosed,
             DATEDIFF(SECOND, MIN(EventEndDate), MAX(EventEndDate)) DurationOpenSec
    FROM     SetGroup
    GROUP BY DoorName, grp
    HAVING   COUNT(*) > 1
GO
DoorName | DoorOpen            | DoorClosed          | DurationOpenSec
:------- | :------------------ | :------------------ | --------------:
Door 001 | 23/03/2017 16:50:42 | 23/03/2017 16:54:15 |             213
Door 001 | 23/03/2017 18:22:37 | 23/03/2017 18:28:01 |             324
Door 001 | 23/03/2017 18:29:40 | 23/03/2017 18:34:53 |             313
Door 001 | 23/03/2017 18:42:56 | 23/03/2017 18:46:32 |             216

dbfiddle here

  • Many thanx!. I understand the major part I have to dive into 'COALESCE'. – Sven Apr 8 '17 at 22:03
  • COALESCE returns the first NOT NULL member – McNets Apr 8 '17 at 22:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.