Count the time difference between specific events in SQL Server 2019 or pandas

Karthik Venkatraman 0
Viewed 42

I am working on an infusion dataset in which I need to find the time duration between the infusion stop and other infusion events.

This is a screenshot of the dataset:

Sample

In the screenshot, the first event status is STOPPED at 06:28:31 and the infusion started to run by 09:10:54. Hence the total seconds from the stop to run is 9743 which has to get populated for row 1 in a new column. Likewise 16:50:38 the pump stopped and there was an alarm by 06:04:07 so the difference would be approximately 13 hours. so on row 5, I need the difference value of 13 hours. I need this difference to be found for the entire data where ever I have stopped and the followed by running or stopped alarm infusion status.

I was able to find the difference between each running and stopped alarm status from the stopped event. However its getting populated for all places where i have stopped.

This is the SQL code I use:

SELECT 
    InfusionStatus, InfusionID, EventDescription, 
    Time AS event_time,
    IIF ((InfusionStatus = 'STOPPED'),
         DATEDIFF(SECOND, A1.Time, ISNULL((SELECT TOP 1 Time
                                           FROM table1
                                           WHERE InfusionID = A1.InfusionID
                                             AND SiteNumber = A1.SiteNumber
                                             AND SerialNumber = A1.SerialNumber
                                             AND Time >= A1.Time
                                             AND (InfusionStatus = 'RUNNING' OR 
                                                  InfusionStatus = 'STOPPED_ALARM')
                                           ORDER BY Time ASC), A1.time)), 0) AS stop_run_event_duration_secs
FROM 
    dbo.table1 A1

The output that I am getting is like this:

output

Basically I don't want the difference to be populated in the area's I have marked as "X". The difference has to get populated only for the first stopped event.

Link for the data:

Data Link

I can also go with Python code for determining this.

Any help would be greatly appreciated.

sql-server pandas sql
1 Answers

Using Common Table Expression (CTE):

;WITH cte AS
(
    SELECT 
            *
        -- Every time the InfusionStatus changes into STOPPED, RUNNING or
        -- STOPPED_ALARM for the first time, set IsFirstStatusChange = 1
        ,   CASE
                WHEN    InfusionStatus IN ('STOPPED', 'RUNNING', 'STOPPED_ALARM')
                    AND InfusionStatus != LAG(InfusionStatus, 1, '') OVER (ORDER BY Time)
                            THEN 1
                            ELSE 0
            END                         AS IsFirstStatusChange
    FROM    Infusion_data
)

-- For each status change, calculate the duration from the previous change
-- I don't know your requirements around handling STOPPED so I set Duration to
-- NULL for these rows
SELECT      *
        ,   IIF(InfusionStatus = 'STOPPED', NULL, DATEDIFF(SECOND, LAG(Time) OVER (ORDER BY Time), Time)) AS Duration
FROM        cte
WHERE       IsFirstStatusChange = 1
ORDER BY    Time
Code Different0
Related