Add a column with the Difference between the current row and a most recent specific row

Question

I want to add a separate column which calculate the difference of time value between DOOR CLOSE and DOOR OPEN and returns the difference and if the row does not contain DOOR CLOSE msg it returns null value

|

Answer 1

Hi @Bhalala, Pragnesh

Try this query:

;WITH CTE AS
(
SELECT *, SUM(CASE WHEN EventMessage LIKE 'DOOR%'THEN 1 ELSE 0 END)OVER(ORDER BY TriggerTime) AS Mark
FROM #Events
)
SELECT C1.TriggerTime
      ,C1.EventMessage
	  ,CAST((CAST(C1.TriggerTime AS datetime)-CAST(C2.TriggerTime AS datetime)) as time(0)) Open_Time 
FROM CTE C1 LEFT JOIN CTE C2 ON C1.EventMessage='DOOR CLOSE' AND C2.EventMessage='DOOR OPEN' AND C2.Mark+1=C1.Mark

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

This should provide what you need:

DROP TABLE IF EXISTS #Events;

CREATE TABLE #Events
(
    TriggerTime time(0) NOT NULL,
    EventMessage varchar(50) NOT NULL
);

INSERT #Events (TriggerTime, EventMessage)
VALUES ('3:31:00', 'Task 1'),
       ('3:42:53', 'DOOR OPEN'),
       ('3:43:53', 'Task 2'),
       ('3:57:00', 'DOOR CLOSE'),
       ('3:58:00', 'Task 3'),
       ('4:10:00', 'Task 4'),
       ('4:13:00', 'Task 5'),
       ('4:20:22', 'DOOR OPEN'),
       ('4:21:00', 'Task 7'),
       ('4:24:14', 'Task 8'),
       ('4:27:00', 'Task 9'),
       ('4:28:00', 'Task 10'),
       ('4:43:00', 'DOOR CLOSE');

SELECT e.TriggerTime,
       e.EventMessage,
       CASE WHEN lo.EventMessage = 'DOOR OPEN'
            THEN DATEADD(second, 0 - DATEPART(hour, lo.TriggerTime) * 60 * 60
                                   - DATEPART(minute, lo.TriggerTime) * 60
                                   - DATEPART(second, lo.TriggerTime), e.TriggerTime)
       END AS Duration
FROM #Events AS e
OUTER APPLY
(
    SELECT TOP(1) el.TriggerTime, el.EventMessage
    FROM #Events AS el
    WHERE el.TriggerTime < e.TriggerTime
    AND el.EventMessage LIKE 'DOOR%'
    AND e.EventMessage = 'DOOR CLOSE'
    ORDER BY el.TriggerTime DESC
) AS lo;

There are a few important notes though:

Having just the time by itself for when the trigger time happened is problematic, unless the difference between opens and closes never crosses midnight. Otherwise, you'd need a date there as well.

SQL Server doesn't have an interval or duration data type, so I've gone with a simple way to just subtract the two times to produce another time as the output.

What I would normally do, is to subtract the start and ending times (with their dates), and get the number of seconds in total, then convert that to a duration. In the SDU Tools that I publish (free), you can find a function that converts seconds to a duration. (https://sdutools.sqldownunder.com/) You could use that, or copy the code from it.

I've allowed for the case where opens and closes get missed for some reason. (Often happens in real systems). So the code works by looking up, for each row that's a DOOR CLOSE, the latest previous row that starts with DOOR. Then in the SELECT, we process it if it was an OPEN.

Another hint is that you'll get answers quicker in these forums if you include code to create the test table and populate it, or at least editable text, rather than a picture of the data.

Hope that gets you started.