sqlsql-serverdatabaseetlquery-performance
How to select one row when two or more consecutive rows have same statuses
Explanation: I have a table that maintains data of sensors and their statuses (Running / Stopped) with date time. As shown in the below image:
I transform this data into this:
Using this query:
select * from (
Select A.TagName,
(select top 1 EventTime from PumpRunCounts_perf where status = 'Running' and EventTime<A.EventTime order by 1 desc) StartTime,
A.EventTime, A.Statuses
from (select * from PumpRunCounts_perf where status = 'Stopped')A
)A
The problem arises however when consecutive statuses are present, as shown below
What I want to achieve in consecutive statuses is that when consecutive 'Running' statuses are given, pick the most older one and when there are consecutive 'Stopped' statuses pick the latest one.
Like this:
I have used the cursors it gets the job done, but with over 50 hundred thousand rows it takes days to complete. Not sharing cursor code, for the sake of simplicity, let me know if required.
Please help me achieve this task. Thanks
Please see sample table schema and data
CREATE TABLE [dbo].[pumpruncounts_pref](
[ID] [nvarchar](36) NULL,
[EventTime] [datetime2](7) NULL,
[Tagname] [nvarchar](4000) NULL,
[Status] [nvarchar](4000) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'C6A41E1E-2C5B-4A7A-8858-67EBA622DC24', CAST(N'2020-07-22T23:06:00.0700000' AS DateTime2), N'PS44_P06.RunS', N'Stopped')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'EDDE7B77-4A59-4BE0-95F5-31532BF2CBA1', CAST(N'2020-07-21T12:06:45.5640000' AS DateTime2), N'PS44_P06.RunS', N'Running')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'B44CAC7C-B648-44BD-9BC1-B647CE448124', CAST(N'2020-07-23T19:10:50.3870000' AS DateTime2), N'PS44_P06.RunS', N'Running')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'093C6C46-2A38-4A71-83B2-FDF849B06016', CAST(N'2020-07-18T05:54:52.7430000' AS DateTime2), N'PS44_P06.RunS', N'Running')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'69A1610E-6859-4C56-845C-0A4EFF215A41', CAST(N'2020-07-20T04:59:45.2150000' AS DateTime2), N'PS44_P06.RunS', N'Running')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'37DD901A-F2A0-4142-887D-D22CB38BB1F2', CAST(N'2020-07-20T03:19:40.7580000' AS DateTime2), N'PS44_P06.RunS', N'Stopped')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'1D3F54F6-A129-43BB-B30B-4782AB587711', CAST(N'2020-07-24T03:39:44.4840000' AS DateTime2), N'PS44_P06.RunS', N'Stopped')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'8A9C9058-4721-4C4F-A0D4-0B93B48AEC1A', CAST(N'2020-07-22T01:57:20.8030000' AS DateTime2), N'PS44_P06.RunS', N'Stopped')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'A41E1295-C196-4E24-BDF4-62FC0B321BE3', CAST(N'2020-07-23T10:36:46.2140000' AS DateTime2), N'PS44_P06.RunS', N'Stopped')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'7EAB2F61-EC61-448B-AA41-5C097C2F01EE', CAST(N'2020-07-20T13:41:35.4600000' AS DateTime2), N'PS44_P06.RunS', N'Running')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'75746488-03B4-4639-B1AD-9882297B8FA6', CAST(N'2020-07-23T20:01:12.6110000' AS DateTime2), N'PS44_P06.RunS', N'Running')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'3ADA8A20-0E79-4AAA-A7A7-FC17C7FC21E4', CAST(N'2020-07-19T04:12:21.9360000' AS DateTime2), N'PS44_P06.RunS', N'Stopped')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'4CCEF052-46B1-4674-8278-C6649FD42C85', CAST(N'2020-07-23T10:10:33.3180000' AS DateTime2), N'PS44_P06.RunS', N'Running')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'707EB5F2-CF3B-4699-9C59-C883615897C5', CAST(N'2020-07-21T21:54:10.1870000' AS DateTime2), N'PS44_P06.RunS', N'Stopped')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'E7104F6D-82E3-49F5-B446-6A371A1E558A', CAST(N'2020-07-21T21:05:57.5550000' AS DateTime2), N'PS44_P06.RunS', N'Stopped')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'23358E48-E947-4273-ACCC-E52A0A8DB137', CAST(N'2020-07-22T16:33:22.3440000' AS DateTime2), N'PS44_P06.RunS', N'Running')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'274C023F-5E7B-4626-AD96-CA9BB42EE6A0', CAST(N'2020-07-22T03:34:21.3950000' AS DateTime2), N'PS44_P06.RunS', N'Stopped')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'C58C6FB7-478E-4E32-AB08-535218AC3306', CAST(N'2020-07-24T04:12:08.0600000' AS DateTime2), N'PS44_P06.RunS', N'Stopped')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'3F4E8D3C-0101-4A74-93CC-922F7700C83B', CAST(N'2020-07-20T19:04:40.4240000' AS DateTime2), N'PS44_P06.RunS', N'Stopped')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'A1C363E2-3F14-4F9A-80B8-DC8B6634C2AB', CAST(N'2020-07-20T22:20:48.7130000' AS DateTime2), N'PS44_P06.RunS', N'Stopped')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'95DC2DD7-CD27-4783-864D-4287A90DEDB3', CAST(N'2020-07-19T21:27:06.5570000' AS DateTime2), N'PS44_P06.RunS', N'Stopped')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'9D3BAA7D-569D-414C-ADE0-F5263BACE1FF', CAST(N'2020-07-20T16:39:15.2270000' AS DateTime2), N'PS44_P06.RunS', N'Stopped')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'B1C3AE46-3713-40A9-BA46-C3209EB7120C', CAST(N'2020-07-24T04:40:33.3250000' AS DateTime2), N'PS44_P06.RunS', N'Running')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'DC1F0A0A-D767-494F-968A-07B6823CB26D', CAST(N'2020-07-20T20:14:50.2330000' AS DateTime2), N'PS44_P06.RunS', N'Running')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'B497FA37-0E0F-4C19-9DF3-16735799ED02', CAST(N'2020-07-19T14:25:49.0880000' AS DateTime2), N'PS44_P06.RunS', N'Stopped')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'CAAED751-4EE5-4FEF-98E2-0EEE8243CC1C', CAST(N'2020-07-22T04:06:56.9780000' AS DateTime2), N'PS44_P06.RunS', N'Stopped')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'46258565-5C3F-45FB-AA36-78E3AAE0E88B', CAST(N'2020-07-23T03:43:54.3460000' AS DateTime2), N'PS44_P06.RunS', N'Running')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'F7A1DB90-11D0-489F-A7BF-3D882153C6F3', CAST(N'2020-07-21T04:13:58.3370000' AS DateTime2), N'PS44_P06.RunS', N'Running')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'38496DC3-BC8D-4D4C-9F0F-A0FC6285C009', CAST(N'2020-07-24T04:06:56.3970000' AS DateTime2), N'PS44_P06.RunS', N'Running')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'3FE2CA7D-D059-403C-8BF2-9E2167B2D758', CAST(N'2020-07-23T17:26:22.8540000' AS DateTime2), N'PS44_P06.RunS', N'Running')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'EB1591B1-73D8-4755-9B4C-0222ADA4BA2D', CAST(N'2020-07-18T05:59:27.6120000' AS DateTime2), N'PS44_P06.RunS', N'Stopped')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'44743BDB-E084-4B5F-86A0-99812EC1452D', CAST(N'2020-07-19T04:08:14.9520000' AS DateTime2), N'PS44_P06.RunS', N'Running')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'C9AC6855-1BDF-4B5C-9514-1CAE0D86E1D0', CAST(N'2020-07-22T20:51:39.3850000' AS DateTime2), N'PS44_P06.RunS', N'Running')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'7E65B1CD-EE80-48C7-8DFD-F5A0F8C1D4E5', CAST(N'2020-07-18T16:44:55.2720000' AS DateTime2), N'PS44_P06.RunS', N'Stopped')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'0CAFDFDF-CA04-4B8E-81FD-E54C96338120', CAST(N'2020-07-23T02:42:17.7990000' AS DateTime2), N'PS44_P06.RunS', N'Stopped')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'DE7EA0C5-2EB9-4433-B99D-253711F0D990', CAST(N'2020-07-22T03:29:24.8290000' AS DateTime2), N'PS44_P06.RunS', N'Running')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'DB459A4A-F03E-4911-9134-AB74C5763A29', CAST(N'2020-07-19T15:59:08.0170000' AS DateTime2), N'PS44_P06.RunS', N'Running')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'C0AAABE7-D3C8-4E58-8501-44F2807992F0', CAST(N'2020-07-21T03:37:42.8400000' AS DateTime2), N'PS44_P06.RunS', N'Running')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'F9E84C95-D1B8-42BE-8E51-A1E5BC63823C', CAST(N'2020-07-22T02:22:35.4930000' AS DateTime2), N'PS44_P06.RunS', N'Running')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'3C12D9B6-38FC-4CD0-B668-EC583D072DC0', CAST(N'2020-07-21T02:30:25.0890000' AS DateTime2), N'PS44_P06.RunS', N'Running')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'D8B005DF-3443-49BD-ACFD-803CFDDEF58D', CAST(N'2020-07-21T04:47:35.6360000' AS DateTime2), N'PS44_P06.RunS', N'Running')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'3D6D5222-9B25-45C1-A73A-2E31F9DED4E3', CAST(N'2020-07-23T15:16:24.2680000' AS DateTime2), N'PS44_P06.RunS', N'Stopped')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'F9EC2A51-0F1F-4BCB-8FAE-DE70B89BB590', CAST(N'2020-07-23T16:56:09.4620000' AS DateTime2), N'PS44_P06.RunS', N'Stopped')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'433A4288-2FE3-4C7B-9205-16E98D1A376B', CAST(N'2020-07-19T14:47:55.5760000' AS DateTime2), N'PS44_P06.RunS', N'Running')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'D05ED43A-244F-43B5-B778-38442CF2F6A4', CAST(N'2020-07-22T23:06:41.4140000' AS DateTime2), N'PS44_P06.RunS', N'Stopped')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'D1399731-0F0F-4019-A8A5-12A7E2CD9D36', CAST(N'2020-07-21T04:18:24.7070000' AS DateTime2), N'PS44_P06.RunS', N'Stopped')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'B8231929-2062-45A4-A7E6-939EF4E994F2', CAST(N'2020-07-20T04:29:50.5050000' AS DateTime2), N'PS44_P06.RunS', N'Stopped')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'5D88B78F-6178-4B4E-8ACA-FD888E916DB5', CAST(N'2020-07-22T10:33:41.0020000' AS DateTime2), N'PS44_P06.RunS', N'Running')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'4FB517BD-401C-451A-AE35-EEF80D5262AE', CAST(N'2020-07-22T04:01:47.3900000' AS DateTime2), N'PS44_P06.RunS', N'Running')
GO
INSERT [dbo].[pumpruncounts_pref] ([ID], [EventTime], [Tagname], [Status]) VALUES (N'3E673EB3-BC3D-40B7-9BDA-ABDF192CE012', CAST(N'2020-07-22T20:28:47.4780000' AS DateTime2), N'PS44_P06.RunS', N'Stopped')
GO
Solution
It seems odd to me that you want the later stopped row rather than the first. But that is the question you asked here.
One approach uses lag() and lead() to keep only the "Running" row following a stopped (or NULL) and "Stopped" rows where the next row is "Running" (or NULL).
Then a simple lead() and filter gets what you want:
select tagname, eventtime as starttime, next_eventtime as endtime
from (select p.*,
lead(eventtime) over (partition by tagname order by eventtime) as next_eventtime
from (select p.*,
lag(status) over (partition by tagname order by eventtime) as prev_status,
lead(status) over (partition by tagname order by eventtime) as next_status
from pumpruncounts_pref p
) p
where ( status = 'Running' and (prev_status is null or prev_status = 'Stopped') ) or
( status = 'Stopped' and (next_status is null or next_status = 'Running') )
) p
where status = 'Running';
Here is a db<>fiddle.
If you actually want the first stop in a sequence (which makes more sense to me), then you can tweak the logic for stops to mimic the starts (i.e. looking at the previous row rather than the next).
For performance, add an index on (tagname, eventtime, status).
- Math.Sin() gives incorrect value
- How to run my python script when the sunOS is start booting
- Express-session: not resetting cookie expiration on each request
- Getting a stack overflow exception when normalizing a vector
- Edit default summary function in R gives error for multiple variables
- What was a For loop? Why isn't it needed in R?
- How to use download button in shiny and save results in various formats (csv, texte, pdf, spss...)?
- Why are there two assignment operators, `<-` and `->` in R?
- lm()$assign: what is it?
- How to get the value of list(...) in R and S functions
- Design matrix for MLM from library(lme4) with fixed and random effects
- how to generate elements not included in my sample
- Create a matrix with gradually changing values without a for loop
- Emacs ESS and S-plus ( S+ ) 8.1 compatability
- How to lag date-index in a time-series in R?
- Nonlinear regression in R / S
- Calling R from S-Plus?