Sql-server – Duration how long a door is open, but same status records can be present more than once

sql servert-sql

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?

Best Answer

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

Related Solutions

SQL Server – Is It Legal to CREATE and DROP #SomeTable Multiple Times?

No, the parser won't let you create the same #temp table twice in the same batch (and this has nothing to do with SSMS). It doesn't even matter if only one copy of the #temp table could ever be created; for example, in the following conditional logic, which to humans could obviously only ever execute one branch, SQL Server can't see that:

IF 1 = 1
BEGIN
  CREATE TABLE #x(i INT);
  DROP TABLE #x;
END
ELSE
BEGIN
  CREATE TABLE #x(j INT);
  DROP TABLE #x;
END

Msg 2714, Level 16, State 1, Line 8
There is already an object named '#x' in the database.

And to prove it's not SSMS complaining at compile time (a common misconception):

DECLARE @sql NVARCHAR(MAX) = N'IF 1 = 1
BEGIN
  CREATE TABLE #x(i INT);
  DROP TABLE #x;
END
ELSE
BEGIN
  CREATE TABLE #x(j INT);
  DROP TABLE #x;
END';

EXEC sp_executesql @sql;

Yields the exact same error, even though SSMS does not try to parse or validate dynamic SQL before sending it to the server via sp_executesql.

The fix, of course, is to re-use the same #temp table instead of dropping, use a different #temp table each time, or don't use #temp tables in the first place.

This is not something you should ever expect SQL Server to handle better. In other words, get used to whatever workaround you decide on.

See also this related answer on Stack Overflow that gives an alternate explanation:

Can I recreate a temp table after dropping it?

Sql-server – How to write windowing query which sums a column to create discrete buckets

I am not sure what type of performance you are looking for, but if CLR or external app is not an option, a cursor is all that is left. On my aged laptop I get through 1,000,000 rows in about 100 seconds using the following solution. The nice thing about it is that it scales linearly, so I would be looking at a little about 20 minutes to run through the entire thing. With a decent server you will be faster, but not an order of magnitude, so it would still take several minutes to complete this. If this is a one off process, you probably can afford the slowness. If you need to run this as a report or similar regularly, you might want to store the values in the same table un update them as new rows get added, e.g. in a trigger.

Anyway, here is the code:

IF OBJECT_ID('dbo.MyTable') IS NOT NULL DROP TABLE dbo.MyTable;

CREATE TABLE dbo.MyTable(
 Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
 v NUMERIC(5,3) DEFAULT ABS(CHECKSUM(NEWID())%100)/100.0
);


MERGE dbo.MyTable T
USING (SELECT TOP(1000000) 1 X FROM sys.system_internals_partition_columns A,sys.system_internals_partition_columns B,sys.system_internals_partition_columns C,sys.system_internals_partition_columns D)X
ON(1=0)
WHEN NOT MATCHED THEN
INSERT DEFAULT VALUES;

--SELECT * FROM dbo.MyTable

DECLARE @st DATETIME2 = SYSUTCDATETIME();
DECLARE cur CURSOR FAST_FORWARD FOR
  SELECT Id,v FROM dbo.MyTable
  ORDER BY Id;

DECLARE @id INT;
DECLARE @v NUMERIC(5,3);
DECLARE @running_total NUMERIC(6,3) = 0;
DECLARE @bucket INT = 1;

CREATE TABLE #t(
 id INT PRIMARY KEY CLUSTERED,
 v NUMERIC(5,3),
 bucket INT,
 running_total NUMERIC(6,3)
);

OPEN cur;
WHILE(1=1)
BEGIN
  FETCH NEXT FROM cur INTO @id,@v;
  IF(@@FETCH_STATUS <> 0) BREAK;
  IF(@running_total + @v > 1)
  BEGIN
    SET @running_total = 0;
    SET @bucket += 1;
  END;
  SET @running_total += @v;
  INSERT INTO #t(id,v,bucket,running_total)
  VALUES(@id,@v,@bucket, @running_total);
END;
CLOSE cur;
DEALLOCATE cur;
SELECT DATEDIFF(SECOND,@st,SYSUTCDATETIME());
SELECT * FROM #t;

GO 
DROP TABLE #t;

It drops and recreates the table MyTable, fills it with 1000000 rows and then goes to work.

The cursor copies each row into a temp table while running the calculations. At the end the select returns the calculated results. You might be a little faster if you don't copy the data around but do an in-place update instead.

If you have an option to upgrade to SQL 2012 you can look at the new window-spool supported moving window aggregates, that should give you better performance.

On a side note, if you have an assembly installed with permission_set=safe, you can do more bad stuff to a server with standard T-SQL than with the assembly, so I would keep working on removing that barrier - You have a good use case here where CLR really would help you.

Best Answer

Related Solutions

SQL Server – Is It Legal to CREATE and DROP #SomeTable Multiple Times?

Sql-server – How to write windowing query which sums a column to create discrete buckets

Related Question