Home > OS >  Calculate difference between rows based on a condition in SQL Server 2019
Calculate difference between rows based on a condition in SQL Server 2019

Time：09-18

I am working on a code which has few events transition status and the duration of the transition status. Below is the screenshot of the data.

data sample

What I need to find is the difference of duration for each event transition that has the value as "True". For example, in the above screenshot, row 4 has transition value as "True" with duration secs as 9743 and row 5 has transition value of "True" and duration as 37327. Hence I need to find the difference between these two rows. Likewise difference between row 9 and row 5 where i have the transition value as True.

I tried with the below code however it didn't work. I can filter only the event_transitions that has True and can find out the difference however, I need the status "False" too. hence wont be able to filter out the data.

SELECT CTE.SerialNumber,
       CTE.Time,
       event_transition,
       CASE WHEN CTE.event_transition = 'True' THEN CTE.event_duration_in_secs ELSE 0 END AS duration_secs,
       cte.event_transition - LAG(CASE WHEN CTE.event_transition = 'True' THEN CTE.event_duration_in_secs ELSE 0 END) OVER (ORDER BY Time) AS next_time
--       CTE.event_duration_in_secs
  FROM CTE
  ORDER BY CTE.Time, CTE.Sequence_number

Sample data as follows:

CREATE TABLE [dbo].[sample_transition_data]
(
    [SerialNumber] [int] NOT NULL,
    [Time] [datetime] NOT NULL,
    [event_transition] [varchar](5) NOT NULL,
    [duration_secs] [int] NULL
) ON [PRIMARY]

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) 
VALUES (748946, CAST(N'2020-10-07T06:28:31.000' AS DateTime), N'True', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) 
VALUES (748946, CAST(N'2020-10-07T06:28:47.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) 
VALUES (748946, CAST(N'2020-10-07T06:28:52.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) 
VALUES (748946, CAST(N'2020-10-07T09:10:54.000' AS DateTime), N'True', 9743)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) 
VALUES (748946, CAST(N'2020-10-07T16:50:38.000' AS DateTime), N'True', 37327)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) 
VALUES (748946, CAST(N'2020-10-07T16:50:59.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) 
VALUES (748946, CAST(N'2020-10-07T16:51:02.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) 
VALUES (748946, CAST(N'2020-10-08T06:04:06.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) 
VALUES (748946, CAST(N'2020-10-08T06:04:07.000' AS DateTime), N'True', 84936)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) 
VALUES (748946, CAST(N'2020-10-08T06:04:09.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) 
VALUES (748946, CAST(N'2020-10-08T06:04:09.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) 
VALUES (748946, CAST(N'2020-10-08T06:04:09.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) 
VALUES (748946, CAST(N'2020-10-08T06:04:11.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) 
VALUES (748946, CAST(N'2020-10-08T06:24:11.000' AS DateTime), N'True', 86140)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) 
VALUES (748946, CAST(N'2020-10-08T06:25:19.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) 
VALUES (748946, CAST(N'2020-10-08T06:25:20.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) 
VALUES (748946, CAST(N'2020-10-08T06:25:26.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) 
VALUES (748946, CAST(N'2020-10-08T06:25:26.000' AS DateTime), N'True', 86215)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) 
VALUES (748946, CAST(N'2020-10-08T06:25:28.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) 
VALUES (748946, CAST(N'2020-10-08T06:25:29.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) 
VALUES (748946, CAST(N'2020-10-08T06:25:31.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) 
VALUES (748946, CAST(N'2020-10-08T06:25:31.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) 
VALUES (748946, CAST(N'2020-10-08T06:25:41.000' AS DateTime), N'True', 86230)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) 
VALUES (748946, CAST(N'2020-10-08T07:41:16.000' AS DateTime), N'True', 90765)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) 
VALUES (748946, CAST(N'2020-10-08T11:40:34.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) 
VALUES (748946, CAST(N'2020-10-08T11:41:32.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) 
VALUES (748946, CAST(N'2020-10-08T13:20:32.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) 
VALUES (748946, CAST(N'2020-10-08T13:20:33.000' AS DateTime), N'True', 111122)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) 
VALUES (748946, CAST(N'2020-10-08T13:20:33.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) 
VALUES (748946, CAST(N'2020-10-08T13:20:33.000' AS DateTime), N'True', 111122)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) 
VALUES (748946, CAST(N'2020-10-09T06:21:03.000' AS DateTime), N'True', 172352)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) 
VALUES (748946, CAST(N'2020-10-09T06:21:38.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) 
VALUES (748946, CAST(N'2020-10-09T06:21:38.000' AS DateTime), N'True', 172387)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) 
VALUES (748946, CAST(N'2020-10-09T06:21:39.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) 
VALUES (748946, CAST(N'2020-10-09T06:21:39.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) 
VALUES (748946, CAST(N'2020-10-09T06:21:42.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) 
VALUES (748946, CAST(N'2020-10-09T06:21:46.000' AS DateTime), N'True', 172395)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) 
VALUES (748946, CAST(N'2020-10-09T06:21:52.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) 
VALUES (748946, CAST(N'2020-10-09T06:21:56.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) 
VALUES (748946, CAST(N'2020-10-09T06:21:57.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) 
VALUES (748946, CAST(N'2020-10-09T06:22:05.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) 
VALUES (748946, CAST(N'2020-10-09T07:54:58.000' AS DateTime), N'True', 177987)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) 
VALUES (748946, CAST(N'2020-10-09T09:42:16.000' AS DateTime), N'True', 184425)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T09:42:18.000' AS DateTime), N'True', 184427)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T11:15:50.000' AS DateTime), N'True', 190039)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T11:15:50.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T11:15:52.000' AS DateTime), N'True', 190041)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T13:08:11.000' AS DateTime), N'True', 196780)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T13:08:12.000' AS DateTime), N'True', 196781)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T13:08:12.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T13:08:13.000' AS DateTime), N'True', 196782)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T13:08:15.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T13:08:16.000' AS DateTime), N'True', 196785)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:27:58.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:27:58.000' AS DateTime), N'True', 201567)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:28:02.000' AS DateTime), N'True', 201571)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:28:08.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:28:08.000' AS DateTime), N'True', 201577)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:28:11.000' AS DateTime), N'True', 201580)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:28:11.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:28:13.000' AS DateTime), N'True', 201582)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:28:14.000' AS DateTime), N'True', 201583)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:28:21.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:28:21.000' AS DateTime), N'True', 201590)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:28:23.000' AS DateTime), N'True', 201592)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:28:23.000' AS DateTime), N'True', 201592)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:28:25.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:28:27.000' AS DateTime), N'True', 201596)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:28:34.000' AS DateTime), N'True', 201603)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:28:34.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:28:35.000' AS DateTime), N'True', 201604)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:28:36.000' AS DateTime), N'True', 201605)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:28:37.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:28:39.000' AS DateTime), N'True', 201608)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:28:46.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:28:46.000' AS DateTime), N'True', 201615)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:28:49.000' AS DateTime), N'True', 201618)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:28:54.000' AS DateTime), N'True', 201623)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:28:54.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:28:58.000' AS DateTime), N'True', 201627)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:29:08.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:29:08.000' AS DateTime), N'True', 201637)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:29:14.000' AS DateTime), N'True', 201643)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:29:15.000' AS DateTime), N'True', 201644)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:29:16.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:29:19.000' AS DateTime), N'True', 201648)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:29:29.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:29:29.000' AS DateTime), N'True', 201658)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:29:30.000' AS DateTime), N'True', 201659)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:29:31.000' AS DateTime), N'True', 201660)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:29:32.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:29:38.000' AS DateTime), N'True', 201667)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:31:53.000' AS DateTime), N'True', 201802)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:31:53.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:31:57.000' AS DateTime), N'True', 201806)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:32:10.000' AS DateTime), N'True', 201819)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:32:11.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:32:15.000' AS DateTime), N'True', 201824)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:32:15.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:32:17.000' AS DateTime), N'True', 201826)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:32:18.000' AS DateTime), N'True', 201827)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:32:27.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:32:27.000' AS DateTime), N'True', 201836)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:32:32.000' AS DateTime), N'True', 201841)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:32:33.000' AS DateTime), N'True', 201842)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:32:34.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:32:35.000' AS DateTime), N'True', 201844)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:32:39.000' AS DateTime), N'False', 0)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:32:39.000' AS DateTime), N'True', 201848)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T14:32:40.000' AS DateTime), N'True', 201849)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T15:03:58.000' AS DateTime), N'True', 203727)

INSERT [dbo].[sample_transition_data] ([SerialNumber], [Time], [event_transition], [duration_secs]) VALUES (748946, CAST(N'2020-10-09T15:06:27.000' AS DateTime), N'True', 203876)

Below is the link for the data

DB Fiddle

CodePudding user response：

WITH temp_table AS
    (SELECT
        *,
        duration_secs - LAG(duration_secs) OVER (ORDER BY Time) AS difference 
    FROM CTE
    WHERE event_transition = 'True')

SELECT
    c.SerialNumber,
    c.Time,
    c.event_transition,
    c.duration_secs,
    t.difference
FROM CTE AS c
LEFT JOIN temp_table AS t ON c.SerialNumber = t.SerialNumber AND c.Time = t.Time

CodePudding user response：

Since the 'duration_secs' value will not decreased over the time i.e. next value >= current value (of course when 'event_transition' = true), you can use the running max and lag window functions as the following:

WITH CTE AS
  (
    SELECT *, MAX(duration_secs) OVER (PARTITION BY SerialNumber ORDER BY Time, event_transition) rs  
    FROM sample_transition_data
  )
SELECT SerialNumber, Time, event_transition, duration_secs,
       CASE event_transition
         WHEN 'True' THEN duration_secs - LAG(rs, 1, 0) OVER (PARTITION BY SerialNumber ORDER BY Time) 
         ELSE 0
       END diff
FROM CTE

See a demo.

Page link：https//www.codepudding.com/os/548280.html

Prev:Join 2 tables with row number in query
Next:Best way to model current version/single elevated list item in Django?
  • Related

About Us:  Contact Us      Terms of Service       Privacy Policy

Copyright © 2010-2023，Powered By CodePudding