Duration how long a door is open, but same status records can be present more than once 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?
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(*) &gt; 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