Home > Net >  Calculate Time Difference between consecutive rows based on an condition in SQL Server 2019
Calculate Time Difference between consecutive rows based on an condition in SQL Server 2019

Time：08-19

I need to find the time duration between two dates on consecutive rows based on a condition. The date difference has to be calculated between each PUMP Stop and Pump Run event for each ID value. Below is the screenshot of the sample data

Sample

In the above screenshot, I need to find the time duration between each pump stop and pump run events. The Pump Run event would have multiple types like Pump Run, Pump Run - Auto Restart etc.

I tried with OUTER APPLY and also with CTE by listing the dates individually however it didn't work well as the duration needs to be partitioned within the ID value. For me it started giving the difference without partition.

Below is the desired output that I need and the schema and data.

Output

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[device_data](
    [SiteNumber] [int] NOT NULL,
    [SerialNumber] [int] NOT NULL,
    [Time] [datetime] NOT NULL,
    [InfusionID] [bigint] NULL,
    [InfusionStatus] [nvarchar](100) NULL,
    [EventDescription] [nvarchar](500) NULL,
    [ElapsedTime] [nvarchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-04T07:18:55.000' AS DateTime), 965399291, N'STOPPED', N'Standby activated', N'0:0')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-04T08:06:59.000' AS DateTime), 965399291, N'RUNNING', N'Pump rate updated:; - Rate 22.5 mL/hr', N'0:11')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-04T08:10:31.000' AS DateTime), 965399291, N'STOPPED_ALARM', N'Pump STOP', N'0:15')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-04T08:10:31.000' AS DateTime), 965399291, N'STOPPED_ALARM', N'Downstream Occlusion!', N'0:15')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-04T08:10:37.000' AS DateTime), 965399291, N'STOPPED', N'Downstream Occl Clr''d', N'0:15')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-04T08:10:37.000' AS DateTime), 965399291, N'RUNNING', N'Pump RUN - Auto-restart', N'0:15')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-04T08:14:30.000' AS DateTime), 965399291, N'STOPPED_ALARM', N'Pump STOP', N'0:19')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-04T08:14:30.000' AS DateTime), 965399291, N'STOPPED_ALARM', N'Downstream Occlusion!', N'0:19')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-04T08:14:33.000' AS DateTime), 965399291, N'STOPPED', N'Downstream Occl Clr''d', N'0:19')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-04T08:14:33.000' AS DateTime), 965399291, N'RUNNING', N'Pump RUN - KVO', N'0:19')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-04T08:26:45.000' AS DateTime), 965399291, N'RUNNING', N'Pump rate updated:; - Rate 15 mL/hr', N'0:31')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-04T08:35:14.000' AS DateTime), 965399291, N'RUNNING_TITRATE', N'OK not pressed!; - value not accepted', N'0:39')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-04T08:35:14.000' AS DateTime), 965399291, N'RUNNING_TITRATE', N'Value Entry Timeout!', N'0:39')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-04T08:35:21.000' AS DateTime), 965399291, N'RUNNING_TITRATE', N'Value Entry TO Clr''d', N'0:39')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-04T08:35:21.000' AS DateTime), 965399291, N'STOPPED', N'Pump STOP', N'0:39')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-04T08:35:23.000' AS DateTime), 965399291, N'STOPPED', N'REVIEW key pressed', N'0:39')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-04T08:35:28.000' AS DateTime), 965399291, N'RUNNING', N'Pump RUN', N'0:39')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-04T08:36:39.000' AS DateTime), 965399291, N'RUNNING', N'REVIEW key pressed', N'0:41')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-04T08:36:42.000' AS DateTime), 965399291, N'STOPPED', N'User STOP', N'0:41')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-04T08:36:43.000' AS DateTime), 965399291, N'STOPPED', N'REVIEW key pressed', N'0:41')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-04T08:36:44.000' AS DateTime), 965399291, N'STOPPED', N'Standby activated', N'0:41')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-04T08:50:20.000' AS DateTime), 965399291, N'RUNNING', N'Pump RUN', N'0:41')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-04T08:50:37.000' AS DateTime), 965399291, N'RUNNING_TITRATE', N'OK not pressed!; - value not accepted', N'0:41')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-04T08:50:37.000' AS DateTime), 965399291, N'RUNNING_TITRATE', N'Value Entry Timeout!', N'0:41')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-04T08:50:43.000' AS DateTime), 965399291, N'RUNNING_TITRATE', N'Value Entry TO Clr''d', N'0:41')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-04T08:50:47.000' AS DateTime), 965399291, N'RUNNING', N'Pump rate updated:; - Rate 15 mL/hr', N'0:41')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-04T08:53:06.000' AS DateTime), 965399291, N'RUNNING', N'Pump rate updated:; - Rate 7.5 mL/hr', N'0:43')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-04T09:05:06.000' AS DateTime), 965399291, N'RUNNING_TITRATE', N'OK not pressed!; - value not accepted', N'0:55')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-04T09:05:06.000' AS DateTime), 965399291, N'RUNNING_TITRATE', N'Value Entry Timeout!', N'0:55')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-04T09:05:12.000' AS DateTime), 965399291, N'RUNNING_TITRATE', N'Value Entry TO Clr''d', N'0:55')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-04T09:05:12.000' AS DateTime), 965399291, N'STOPPED', N'User STOP', N'0:55')
GO
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-05T06:20:37.000' AS DateTime), 965399291, N'STOPPED', N'Infusion Complete', N'4:39')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-05T06:20:56.000' AS DateTime), 5966228461, N'STOPPED', N'Weight  - 62  kg', N'0:0')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-05T06:20:58.000' AS DateTime), 5966228461, N'STOPPED', N'Weight  - 62  kg', N'0:0')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-05T06:20:58.000' AS DateTime), 5966228461, N'STOPPED', N'Weight confirmed', N'0:0')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-05T06:21:15.000' AS DateTime), 5966228461, N'STOPPED', N'Standby activated', N'0:0')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-05T06:21:23.000' AS DateTime), 5966228461, N'STOPPED', N'AC plugged in', N'0:0')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-05T06:21:23.000' AS DateTime), 5966228461, N'STOPPED', N'Battery Low!', N'0:0')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-05T06:21:24.000' AS DateTime), 5966228461, N'STOPPED', N'Batt Low Clr''d', N'0:0')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-05T06:23:31.000' AS DateTime), 5966228461, N'STOPPED', N'Inactivity alarm', N'0:0')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-05T06:23:44.000' AS DateTime), 5966228461, N'STOPPED', N'Inactivity Clr''d', N'0:0')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-05T06:25:44.000' AS DateTime), 5966228461, N'STOPPED', N'Inactivity alarm', N'0:0')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-05T06:25:50.000' AS DateTime), 5966228461, N'STOPPED', N'Inactivity Clr''d', N'0:0')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-05T06:25:53.000' AS DateTime), 5966228461, N'STOPPED', N'Standby activated', N'0:0')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-05T07:58:07.000' AS DateTime), 5966228461, N'RUNNING', N'Pump RUN', N'0:0')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-05T08:06:24.000' AS DateTime), 5966228461, N'RUNNING', N'Pump rate updated:; - Rate 55.8 mL/hr', N'0:8')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-05T08:07:31.000' AS DateTime), 5966228461, N'STOPPED_ALARM', N'Pump STOP', N'0:9')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-05T08:07:31.000' AS DateTime), 5966228461, N'STOPPED_ALARM', N'Downstream Occlusion!', N'0:9')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-05T08:07:36.000' AS DateTime), 5966228461, N'STOPPED', N'Downstream Occl Clr''d', N'0:9')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-05T08:07:36.000' AS DateTime), 5966228461, N'RUNNING', N'Pump RUN - Auto-restart', N'0:9')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-05T09:21:30.000' AS DateTime), 5966228461, N'STOPPED_ALARM', N'Pump STOP', N'1:23')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-05T09:21:30.000' AS DateTime), 5966228461, N'STOPPED_ALARM', N'Downstream Occlusion!', N'1:23')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-05T09:21:34.000' AS DateTime), 5966228461, N'STOPPED', N'Downstream Occl Clr''d', N'1:23')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-05T09:21:34.000' AS DateTime), 5966228461, N'RUNNING', N'Pump RUN - Auto-restart', N'1:23')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-05T09:31:19.000' AS DateTime), 5966228461, N'STOPPED', N'Infusion Complete', N'1:33')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-05T09:31:19.000' AS DateTime), 5966228461, N'RUNNING_KVO', N'Pump RUN - KVO', N'1:33')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-05T09:31:20.000' AS DateTime), 5966228461, N'RUNNING_KVO', N'Pump rate updated:; - Rate 1 mL/hr', N'1:33')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-05T09:31:20.000' AS DateTime), 5966228461, N'RUNNING_KVO', N'Infusion Complete Alarm!', N'1:33')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-05T09:31:33.000' AS DateTime), 5966228461, N'STOPPED', N'User STOP', N'1:33')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-05T09:31:34.000' AS DateTime), 5966228461, N'STOPPED', N'Infusion Cmpl Clr''d', N'1:33')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-05T09:31:54.000' AS DateTime), 5966228461, N'RUNNING', N'Pump RUN', N'1:33')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-05T10:08:02.000' AS DateTime), 5966228461, N'RUNNING', N'Pump rate updated:; - Rate 44.6 mL/hr', N'2:9')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-05T10:11:03.000' AS DateTime), 5966228461, N'RUNNING_TITRATE', N'OK not pressed!; - value not accepted', N'2:12')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-05T10:11:03.000' AS DateTime), 5966228461, N'RUNNING_TITRATE', N'Value Entry Timeout!', N'2:12')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-05T10:11:06.000' AS DateTime), 5966228461, N'RUNNING_TITRATE', N'Value Entry TO Clr''d', N'2:12')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-08-05T10:11:07.000' AS DateTime), 5966228461, N'STOPPED', N'User STOP', N'2:12')
GO
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-11-13T09:31:02.000' AS DateTime), 6052778471, N'STOPPED', N'Weight  - 64  kg', N'0:0')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-11-13T09:31:06.000' AS DateTime), 6052778471, N'STOPPED', N'Weight  - 64  kg', N'0:0')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-11-13T09:31:06.000' AS DateTime), 6052778471, N'STOPPED', N'Weight confirmed', N'0:0')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-11-13T09:31:19.000' AS DateTime), 6052778471, N'STOPPED', N'User STOP', N'0:0')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-11-13T09:31:21.000' AS DateTime), 6052778471, N'STOPPED', N'REVIEW key pressed', N'0:0')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-11-13T09:31:23.000' AS DateTime), 6052778471, N'STOPPED', N'Standby activated', N'0:0')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-11-13T10:51:45.000' AS DateTime), 6052778471, N'RUNNING', N'Pump RUN', N'0:0')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-11-13T10:53:37.000' AS DateTime), 6052778471, N'STOPPED_ALARM', N'Pump STOP', N'0:1')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-11-13T10:53:37.000' AS DateTime), 6052778471, N'STOPPED_ALARM', N'Downstream Occlusion!', N'0:1')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-11-13T10:53:46.000' AS DateTime), 6052778471, N'STOPPED', N'Downstream Occl Clr''d', N'0:1')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-11-13T10:53:46.000' AS DateTime), 6052778471, N'RUNNING', N'Pump RUN - Auto-restart', N'0:1')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-11-13T10:54:33.000' AS DateTime), 6052778471, N'RUNNING', N'Pump rate updated:; - Rate 30.7 mL/hr', N'0:2')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-11-13T11:00:44.000' AS DateTime), 6052778471, N'STOPPED_ALARM', N'Pump STOP', N'0:8')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-11-13T11:00:44.000' AS DateTime), 6052778471, N'STOPPED_ALARM', N'Downstream Occlusion!', N'0:8')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-11-13T11:00:59.000' AS DateTime), 6052778471, N'STOPPED', N'Downstream Occl Clr''d', N'0:8')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-11-13T11:00:59.000' AS DateTime), 6052778471, N'RUNNING', N'Pump RUN - Auto-restart', N'0:8')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-11-13T11:11:04.000' AS DateTime), 6052778471, N'STOPPED_ALARM', N'Pump STOP', N'0:18')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-11-13T11:11:04.000' AS DateTime), 6052778471, N'STOPPED_ALARM', N'Downstream Occlusion!', N'0:18')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-11-13T11:11:16.000' AS DateTime), 6052778471, N'STOPPED', N'Downstream Occl Clr''d', N'0:18')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-11-13T11:11:16.000' AS DateTime), 6052778471, N'RUNNING', N'Pump RUN - Auto-restart', N'0:18')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-11-13T11:20:14.000' AS DateTime), 6052778471, N'STOPPED_ALARM', N'Pump STOP', N'0:27')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-11-13T11:20:14.000' AS DateTime), 6052778471, N'STOPPED_ALARM', N'Downstream Occlusion!', N'0:27')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-11-13T11:20:24.000' AS DateTime), 6052778471, N'STOPPED', N'Downstream Occl Clr''d', N'0:27')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-11-13T11:20:24.000' AS DateTime), 6052778471, N'RUNNING', N'Pump RUN - Auto-restart', N'0:27')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-11-13T11:35:09.000' AS DateTime), 6052778471, N'RUNNING', N'Pump rate updated:; - Rate 38.4 mL/hr', N'0:42')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-11-13T11:40:29.000' AS DateTime), 6052778471, N'RUNNING', N'Pump rate updated:; - Rate 46.1 mL/hr', N'0:47')
INSERT [dbo].[device_data] ([SiteNumber], [SerialNumber], [Time], [InfusionID], [InfusionStatus], [EventDescription], [ElapsedTime]) VALUES (4, 3018462, CAST(N'2020-11-13T11:41:44.000' AS DateTime), 6052778471, N'STOPPED_ALARM', N'Pump STOP', N'0:49')
GO

CodePudding user response：

SELECT e.*, IIF(EventDescription like 'Pump STOP%',
    DATEDIFF(SECOND, Time, ISNULL((SELECT TOP 1 Time
                                    FROM @events 
                                    WHERE InfusionID = e.InfusionID 
                                        AND Time >= e.Time
                                        AND EventDescription like 'Pump Run%'
                                    ORDER BY Time ASC), GETDATE())), 0) as Duration
FROM @events e

@events is your [device_data] table. What the query does is it goes over all events and for each stop, looks for next start of the pump for the same InfusionID. If no start of the pump was found then it takes the current time because the pump must be stopped at the moment. Then it subtracts the 2 dates and returns the duration in seconds. I would highly suggest making a table with definitions of all EventDescriptions and using the ID of the description instead of the strings, because doing EventDescription like 'Pump Run%' instead of EventDescriptionID in (1,2,3) is expensive. This is the result: enter image description here

CodePudding user response：

You can use LEAD for this, along with DATEDIFF to get the difference

WITH cte1 AS (
    SELECT * ,
      NextTime = LEAD(Time) OVER (PARTITION BY SiteNumber, SerialNumber, InfusionID ORDER BY Time)
    FROM device_data dd
    WHERE (EventDescription LIKE 'Pump RUN%' OR EventDescription = 'Pump STOP')
)
SELECT *,
  DATEADD(second, DATEDIFF(second, Time, NextTime), CAST('00:00:00' AS time))
FROM cte1
WHERE EventDescription = 'Pump STOP';

db<>fiddle

Page link：https//www.codepudding.com/net/515362.html

Prev:Oracle db. How to get a result set of unique values of pretty much arbitrary amount of columns of th
Next:More efficient way to get aggregate results in different groupings? - SQL
  • Related

About Us:  Contact Us      Terms of Service       Privacy Policy

Copyright © 2010-2023，Powered By CodePudding