I want to add an additional column (Open Time) in my table which calculates the difference between latest DOOR OPEN and DOOR CLOSE. And is triggered by the message DOOR CLOSE. And I want this column to calculate the difference in time every time new rows are added to the column without me having to run the query again:
Date | TriggerTime | EventMessageheader | Open_Time |
---|---|---|---|
07/10/2024 | 3:31:00 | Task 1 | NULL |
07/10/2024 | 3:42:53 | DOOR OPEN | NULL |
07/10/2024 | 3:43:53 | Task 2 | NULL |
07/10/2024 | 3:57:00 | DOOR CLOSE | 0:14:07 |
07/10/2024 | 3:58:00 | Task 3 | NULL |
07/10/2024 | 4:10:00 | Task 4 | NULL |
07/10/2024 | 4:13:00 | Task 5 | NULL |
07/10/2024 | 4:20:22 | DOOR OPEN | NULL |
07/10/2024 | 4:21:00 | Task 7 | NULL |
07/10/2024 | 4:24:14 | Task 8 | NULL |
07/10/2024 | 4:27:00 | Task 9 | NULL |
07/10/2024 | 4:28:00 | Task 10 | NULL |
07/10/2024 | 4:43:00 | DOOR CLOSE | 0:22:38 |
SQL code:
DROP TABLE IF EXISTS #Events;
CREATE TABLE #Events
(
Date date,
TriggerTime time(0) NOT NULL,
EventMessage varchar(50) NOT NULL
);
INSERT #Events (Date,TriggerTime, EventMessage)
VALUES ('07/10/2024', '3:31:00', 'Task 1'),
('07/10/2024', '3:42:53', 'DOOR OPEN'),
('07/10/2024', '3:43:53', 'Task 2'),
('07/10/2024', '3:57:00', 'DOOR CLOSE'),
('07/10/2024', '3:58:00', 'Task 3'),
('07/10/2024', '4:10:00', 'Task 4'),
('07/10/2024', '4:13:00', 'Task 5'),
('07/10/2024', '4:20:22', 'DOOR OPEN'),
('07/10/2024', '4:21:00', 'Task 7'),
('07/10/2024', '4:24:14', 'Task 8'),
('07/10/2024', '4:27:00', 'Task 9'),
('07/10/2024', '4:28:00', 'Task 10'),
('07/10/2024', '4:43:00', 'DOOR CLOSE');
I tried using the following query, it works for calculating the column but I have rerun it to get updated values in open time column
ALTER TABLE #Events ADD Open_Time TIME(0);
-- Update this column with query value
;WITH CTE AS
(
SELECT
*,
SUM(CASE WHEN EventMessage LIKE 'DOOR%'THEN 1 ELSE 0 END)OVER(ORDER BY TriggerTime) AS Mark
FROM
#Events
)
UPDATE C1
SET C1.Open_Time = CAST((CAST(C1.TriggerTime AS datetime)-CAST(C2.TriggerTime AS datetime)) AS time(0))
FROM CTE C1
LEFT JOIN CTE C2 ON C1.EventMessage = 'DOOR CLOSE'
AND C2.EventMessage = 'DOOR OPEN'
AND C2.Mark + 1 = C1.Mark
As I mention in the comments, don't store this in the table, instead calculate it using a VIEW
. Then you can query the said VIEW
instead and it'll always have the correct value.
The VIEW
would look something like:
CREATE VIEW dbo.ViewName AS
SELECT Date,
TriggerTime,
EventMessage,
LAST_VALUE(CASE EventMessage WHEN 'DOOR OPEN' THEN TriggerTime END) IGNORE NULLS OVER (ORDER BY TriggerTime) AS OpenTime
FROM dbo.YourTable;
If you aren't on 2022+ then you can use MAX
, but you'll have to "jump through the hoop" of getting your separate date and time values into a single datetime value, and then you can CONVERT
it back to a time:
CREATE OR ALTER VIEW dbo.ViewName AS
SELECT Date,
TriggerTime,
EventMessage,
CONVERT(time(0),MAX(CASE EventMessage WHEN 'DOOR OPEN' THEN CONVERT(datetime2(0),CONVERT(varchar(10),Date,112) + ' ' + CONVERT(varchar(10),TriggerTime,114)) END) OVER (ORDER BY TriggerTime)) AS OpenTime
FROM dbo.YourTable;
GO