July 21, 2016 at 4:29 am
I have a table used for logging. Let's say this table contains log entries related to requests processing so there is a column 'request_id' identifying request a log entry belongs to.
Now there are, among others, log entries with 'message' column having value 'Start' or 'Stop'. I need to find all requests ('reques_id' values) that have mismatching 'Start' and 'Stop' entries. Mismatch happens when there is a 'Stop' entry without corresponding 'Start' message. 'Start' without 'Stop' is ok. Nesting is not allowed so two consecutive 'Start' messages without 'Stop' in-between is mismatch as well.
How this can be done in T-SQL? Server is SQL-2008 but I would be curious to know solution for any version as well.
July 21, 2016 at 4:50 am
zenm (7/21/2016)
I have a table used for logging. Let's say this table contains log entries related to requests processing so there is a column 'request_id' identifying request a log entry belongs to.Now there are, among others, log entries with 'message' column having value 'Start' or 'Stop'. I need to find all requests ('reques_id' values) that have mismatching 'Start' and 'Stop' entries. Mismatch happens when there is a 'Stop' entry without corresponding 'Start' message. 'Start' without 'Stop' is ok. Nesting is not allowed so two consecutive 'Start' messages without 'Stop' in-between is mismatch as well.
How this can be done in T-SQL? Server is SQL-2008 but I would be curious to know solution for any version as well.
Relatively straight forward, all you have to do is to post the DDL (create table), sample data as an insert statement and the expected results, then we can provide an example of how to do this.
😎
July 21, 2016 at 5:43 am
Here you are 🙂
The table DDL:
CREATE TABLE [dbo].[request_log](
[id] [int] IDENTITY(1,1) NOT NULL,
[request_id] [uniqueidentifier] NOT NULL,
[message] [nvarchar](1024) NOT NULL,
CONSTRAINT [PK_request_log] PRIMARY KEY CLUSTERED ( [id] ASC )
)
Sample data:
SET IDENTITY_INSERT [dbo].[request_log] ON
GO
INSERT [dbo].[request_log] ([id], [request_id], [message]) VALUES (2, N'9b844bfb-f2b4-4aae-90e0-0ca8ae69af1c', N'Step1')
INSERT [dbo].[request_log] ([id], [request_id], [message]) VALUES (3, N'70b1c8d1-ab88-40f0-a9e4-bd22a285f1f8', N'Stop')
INSERT [dbo].[request_log] ([id], [request_id], [message]) VALUES (4, N'9b844bfb-f2b4-4aae-90e0-0ca8ae69af1c', N'Start')
INSERT [dbo].[request_log] ([id], [request_id], [message]) VALUES (5, N'9b844bfb-f2b4-4aae-90e0-0ca8ae69af1c', N'Step2')
INSERT [dbo].[request_log] ([id], [request_id], [message]) VALUES (6, N'70b1c8d1-ab88-40f0-a9e4-bd22a285f1f8', N'Start')
INSERT [dbo].[request_log] ([id], [request_id], [message]) VALUES (7, N'af71b1e8-d0a8-4766-8507-7d9de0fefd2a', N'Step1')
INSERT [dbo].[request_log] ([id], [request_id], [message]) VALUES (8, N'af71b1e8-d0a8-4766-8507-7d9de0fefd2a', N'Step2')
INSERT [dbo].[request_log] ([id], [request_id], [message]) VALUES (9, N'd0abdd87-8ea6-44a2-b7c4-24e00727ecfb', N'Start')
INSERT [dbo].[request_log] ([id], [request_id], [message]) VALUES (10, N'd0abdd87-8ea6-44a2-b7c4-24e00727ecfb', N'Step1')
INSERT [dbo].[request_log] ([id], [request_id], [message]) VALUES (11, N'd0abdd87-8ea6-44a2-b7c4-24e00727ecfb', N'Start')
INSERT [dbo].[request_log] ([id], [request_id], [message]) VALUES (12, N'd0abdd87-8ea6-44a2-b7c4-24e00727ecfb', N'Stop')
INSERT [dbo].[request_log] ([id], [request_id], [message]) VALUES (13, N'730f3b36-a141-4076-9e19-e47c490b3c51', N'Step1')
INSERT [dbo].[request_log] ([id], [request_id], [message]) VALUES (14, N'730f3b36-a141-4076-9e19-e47c490b3c51', N'Stop')
GO
SET IDENTITY_INSERT [dbo].[request_log] OFF
GO
Expected results: following request_id-s should be returned:
70b1c8d1-ab88-40f0-a9e4-bd22a285f1f8 - Start before Stop;
D0ABDD87-8EA6-44A2-B7C4-24E00727ECFB - two Start-s without Stop in-between;
730f3b36-a141-4076-9e19-e47c490b3c51 - Stop without start.
July 21, 2016 at 6:36 am
Good stuff! Here is a quick example which should get you passed this hurdle
😎
USE TEEST;
GO
SET NOCOUNT ON;
--
IF OBJECT_ID(N'dbo.request_log') IS NOT NULL DROP TABLE dbo.request_log;
CREATE TABLE dbo.request_log(
[id] [int] IDENTITY(1,1) NOT NULL,
[request_id] [uniqueidentifier] NOT NULL,
[message] [nvarchar](1024) NOT NULL,
CONSTRAINT [PK_request_log] PRIMARY KEY CLUSTERED ( [id] ASC )
)
--Sample data:
SET IDENTITY_INSERT [dbo].[request_log] ON
GO
INSERT [dbo].[request_log] ([id], [request_id], [message]) VALUES (2, N'9b844bfb-f2b4-4aae-90e0-0ca8ae69af1c', N'Step1')
INSERT [dbo].[request_log] ([id], [request_id], [message]) VALUES (3, N'70b1c8d1-ab88-40f0-a9e4-bd22a285f1f8', N'Stop')
INSERT [dbo].[request_log] ([id], [request_id], [message]) VALUES (4, N'9b844bfb-f2b4-4aae-90e0-0ca8ae69af1c', N'Start')
INSERT [dbo].[request_log] ([id], [request_id], [message]) VALUES (5, N'9b844bfb-f2b4-4aae-90e0-0ca8ae69af1c', N'Step2')
INSERT [dbo].[request_log] ([id], [request_id], [message]) VALUES (6, N'70b1c8d1-ab88-40f0-a9e4-bd22a285f1f8', N'Start')
INSERT [dbo].[request_log] ([id], [request_id], [message]) VALUES (7, N'af71b1e8-d0a8-4766-8507-7d9de0fefd2a', N'Step1')
INSERT [dbo].[request_log] ([id], [request_id], [message]) VALUES (8, N'af71b1e8-d0a8-4766-8507-7d9de0fefd2a', N'Step2')
INSERT [dbo].[request_log] ([id], [request_id], [message]) VALUES (9, N'd0abdd87-8ea6-44a2-b7c4-24e00727ecfb', N'Start')
INSERT [dbo].[request_log] ([id], [request_id], [message]) VALUES (10, N'd0abdd87-8ea6-44a2-b7c4-24e00727ecfb', N'Step1')
INSERT [dbo].[request_log] ([id], [request_id], [message]) VALUES (11, N'd0abdd87-8ea6-44a2-b7c4-24e00727ecfb', N'Start')
INSERT [dbo].[request_log] ([id], [request_id], [message]) VALUES (12, N'd0abdd87-8ea6-44a2-b7c4-24e00727ecfb', N'Stop')
INSERT [dbo].[request_log] ([id], [request_id], [message]) VALUES (13, N'730f3b36-a141-4076-9e19-e47c490b3c51', N'Step1')
INSERT [dbo].[request_log] ([id], [request_id], [message]) VALUES (14, N'730f3b36-a141-4076-9e19-e47c490b3c51', N'Stop')
GO
SET IDENTITY_INSERT [dbo].[request_log] OFF
GO
;WITH BASE_DATA AS
(
SELECT
RL.id
,DENSE_RANK() OVER
(
ORDER BY RL.request_id
) AS REQUEST_KEY
,ROW_NUMBER() OVER
(
PARTITION BY RL.request_id
ORDER BY RL.id
) AS REQUEST_SEQ
,CASE
WHEN RL.message LIKE N'Start%' THEN 1
ELSE 0
END AS IS_START
,CASE
WHEN RL.message LIKE N'Stop%' THEN 1
ELSE 0
END AS IS_STOP
,RL.request_id
,RL.message
FROM dbo.request_log RL
WHERE RL.message LIKE N'Start%'
OR RL.message LIKE N'Stop%'
)
SELECT
BD_START.id
,BD_START.request_id
,CASE
WHEN BD_START.IS_START = 1 AND BD_STOP.IS_START = 1 THEN 'two Start-s without Stop in-between'
WHEN BD_START.IS_START = 1 AND BD_STOP.IS_STOP = 1 THEN 'Start with closing Stop'
WHEN BD_START.IS_STOP = 1 AND BD_STOP.IS_START = 1 THEN 'Stop after opening Start'
WHEN BD_START.IS_START = 1 AND BD_STOP.IS_START IS NULL THEN 'Start without subsequent stop'
WHEN BD_START.IS_STOP = 1 AND BD_STOP.IS_START IS NULL THEN 'Stop without start'
END AS MSG_TXT
FROM BASE_DATA BD_START
LEFT OUTER JOIN BASE_DATA BD_STOP
ON BD_START.REQUEST_KEY = BD_STOP.REQUEST_KEY
AND BD_START.REQUEST_SEQ = BD_STOP.REQUEST_SEQ - 1
;
Output
id request_id MSG_TXT
---- ------------------------------------ -----------------------------------
4 9B844BFB-F2B4-4AAE-90E0-0CA8AE69AF1C Start without subsequent stop
9 D0ABDD87-8EA6-44A2-B7C4-24E00727ECFB two Start-s without Stop in-between
11 D0ABDD87-8EA6-44A2-B7C4-24E00727ECFB Start with closing Stop
12 D0ABDD87-8EA6-44A2-B7C4-24E00727ECFB Stop without start
3 70B1C8D1-AB88-40F0-A9E4-BD22A285F1F8 Stop after opening Start
6 70B1C8D1-AB88-40F0-A9E4-BD22A285F1F8 Start without subsequent stop
14 730F3B36-A141-4076-9E19-E47C490B3C51 Stop without start
July 21, 2016 at 7:41 am
Thank you for the idea! 🙂
July 21, 2016 at 8:19 am
Eirikur Eiriksson (7/21/2016)
Good stuff! Here is a quick example which should get you passed this hurdle😎
USE TEEST;GO
SET NOCOUNT ON;
--
IF OBJECT_ID(N'dbo.request_log') IS NOT NULL DROP TABLE dbo.request_log;
CREATE TABLE dbo.request_log(
[id] [int] IDENTITY(1,1) NOT NULL,
[request_id] [uniqueidentifier] NOT NULL,
[message] [nvarchar](1024) NOT NULL,
CONSTRAINT [PK_request_log] PRIMARY KEY CLUSTERED ( [id] ASC )
)
--Sample data:
SET IDENTITY_INSERT [dbo].[request_log] ON
GO
INSERT [dbo].[request_log] ([id], [request_id], [message]) VALUES (2, N'9b844bfb-f2b4-4aae-90e0-0ca8ae69af1c', N'Step1')
INSERT [dbo].[request_log] ([id], [request_id], [message]) VALUES (3, N'70b1c8d1-ab88-40f0-a9e4-bd22a285f1f8', N'Stop')
INSERT [dbo].[request_log] ([id], [request_id], [message]) VALUES (4, N'9b844bfb-f2b4-4aae-90e0-0ca8ae69af1c', N'Start')
INSERT [dbo].[request_log] ([id], [request_id], [message]) VALUES (5, N'9b844bfb-f2b4-4aae-90e0-0ca8ae69af1c', N'Step2')
INSERT [dbo].[request_log] ([id], [request_id], [message]) VALUES (6, N'70b1c8d1-ab88-40f0-a9e4-bd22a285f1f8', N'Start')
INSERT [dbo].[request_log] ([id], [request_id], [message]) VALUES (7, N'af71b1e8-d0a8-4766-8507-7d9de0fefd2a', N'Step1')
INSERT [dbo].[request_log] ([id], [request_id], [message]) VALUES (8, N'af71b1e8-d0a8-4766-8507-7d9de0fefd2a', N'Step2')
INSERT [dbo].[request_log] ([id], [request_id], [message]) VALUES (9, N'd0abdd87-8ea6-44a2-b7c4-24e00727ecfb', N'Start')
INSERT [dbo].[request_log] ([id], [request_id], [message]) VALUES (10, N'd0abdd87-8ea6-44a2-b7c4-24e00727ecfb', N'Step1')
INSERT [dbo].[request_log] ([id], [request_id], [message]) VALUES (11, N'd0abdd87-8ea6-44a2-b7c4-24e00727ecfb', N'Start')
INSERT [dbo].[request_log] ([id], [request_id], [message]) VALUES (12, N'd0abdd87-8ea6-44a2-b7c4-24e00727ecfb', N'Stop')
INSERT [dbo].[request_log] ([id], [request_id], [message]) VALUES (13, N'730f3b36-a141-4076-9e19-e47c490b3c51', N'Step1')
INSERT [dbo].[request_log] ([id], [request_id], [message]) VALUES (14, N'730f3b36-a141-4076-9e19-e47c490b3c51', N'Stop')
GO
SET IDENTITY_INSERT [dbo].[request_log] OFF
GO
;WITH BASE_DATA AS
(
SELECT
RL.id
,DENSE_RANK() OVER
(
ORDER BY RL.request_id
) AS REQUEST_KEY
,ROW_NUMBER() OVER
(
PARTITION BY RL.request_id
ORDER BY RL.id
) AS REQUEST_SEQ
,CASE
WHEN RL.message LIKE N'Start%' THEN 1
ELSE 0
END AS IS_START
,CASE
WHEN RL.message LIKE N'Stop%' THEN 1
ELSE 0
END AS IS_STOP
,RL.request_id
,RL.message
FROM dbo.request_log RL
WHERE RL.message LIKE N'Start%'
OR RL.message LIKE N'Stop%'
)
SELECT
BD_START.id
,BD_START.request_id
,CASE
WHEN BD_START.IS_START = 1 AND BD_STOP.IS_START = 1 THEN 'two Start-s without Stop in-between'
WHEN BD_START.IS_START = 1 AND BD_STOP.IS_STOP = 1 THEN 'Start with closing Stop'
WHEN BD_START.IS_STOP = 1 AND BD_STOP.IS_START = 1 THEN 'Stop after opening Start'
WHEN BD_START.IS_START = 1 AND BD_STOP.IS_START IS NULL THEN 'Start without subsequent stop'
WHEN BD_START.IS_STOP = 1 AND BD_STOP.IS_START IS NULL THEN 'Stop without start'
END AS MSG_TXT
FROM BASE_DATA BD_START
LEFT OUTER JOIN BASE_DATA BD_STOP
ON BD_START.REQUEST_KEY = BD_STOP.REQUEST_KEY
AND BD_START.REQUEST_SEQ = BD_STOP.REQUEST_SEQ - 1
;
Output
id request_id MSG_TXT---- ------------------------------------ -----------------------------------
4 9B844BFB-F2B4-4AAE-90E0-0CA8AE69AF1C Start without subsequent stop
9 D0ABDD87-8EA6-44A2-B7C4-24E00727ECFB two Start-s without Stop in-between
11 D0ABDD87-8EA6-44A2-B7C4-24E00727ECFB Start with closing Stop
12 D0ABDD87-8EA6-44A2-B7C4-24E00727ECFB Stop without start
3 70B1C8D1-AB88-40F0-A9E4-BD22A285F1F8 Stop after opening Start
6 70B1C8D1-AB88-40F0-A9E4-BD22A285F1F8 Start without subsequent stop
14 730F3B36-A141-4076-9E19-E47C490B3C51 Stop without start
Heh - And you even did it without a LEAD() for SQL 2008. Nice job, Eirikur.
July 21, 2016 at 8:27 am
Ed Wagner (7/21/2016)
Heh - And you even did it without a LEAD() for SQL 2008. Nice job, Eirikur.
Done this or similar to this since 4.2, haven't forgotten it yet;-)
😎
July 21, 2016 at 8:59 am
Eirikur Eiriksson (7/21/2016)
Ed Wagner (7/21/2016)
Heh - And you even did it without a LEAD() for SQL 2008. Nice job, Eirikur.Done this or similar to this since 4.2, haven't forgotten it yet;-)
😎
I don't go back that far with SQL Server, but I do the same thing. 😉
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply