Hello and Question

Reiner · April 6, 2019, 1:08am

Hi Helpers,

I have a Question and i cant solve it by my self.

I tried to react on a SQL Event Table with Events Records in it.

The Event Table has Entrys of Events.

Every Event has a EventTypeID's like 126,127, 128, ...

Every Event has a Executed Flag.

Actually i do Event via Event and send a Single Json Package. Works fine. After it send i set Executed to true.

But the Performance is bad.
I like to send Arrays. But i cant mixed EventTypeID's. I have to construct a Array of the Eventtypes 126 stopped by EventTypeID's 127 and in the way of 127 Array stopped by 126 Entry for Example.

The Table looks like This:

126
126
126
127
126
126
126
127
126
126
126

I have only one Querry to solve both. Get 126 Events or 127 Events in Timeline.

Because i have to update Executeted Flag and react to the Type. But never it should be mixed of 126 and 127.

I neat only the group of 126 in one Querry Result depent on non Executed. but breaked on other TypeID like 127 in timeline order by EventUID.

What i must do in this case of Eventtabale?

Send a Array of 126 (maybe 46 Eevents)
Stopped by a 127 Event in the Eventtable.
Send 127 Events (maybe 2 Events)
Stopped by a 126 Event.
Send a Array of 126 (maybe 46 Eevents)
.......
.......

How i catch this 46 Results and later the other 2 events.

Can anyone help? I need only abstract help.
Best Regards Reiner

ps. Code comes this week. Sorry that i dont add.

yosiasz · April 6, 2019, 1:56am

Please provide sample tables and sample data as follows

Create table #sample1(Id int, name varchar(50))

Insert into #sample1
Select 1, 'sample'

Reiner · April 8, 2019, 10:42pm

The Create Script:

USE [master]
GO

/****** Object: Database [EventsDB] Script Date: 08.04.2019 00:54:17 ******/
CREATE DATABASE [EventsDB]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'EventsDB', FILENAME = N'C:\Events\EventsDB.mdf' , SIZE = 65536KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
LOG ON
( NAME = N'EventsDB_log', FILENAME = N'C:\Events\EventsDB_log.ldf' , SIZE = 65536KB , MAXSIZE = UNLIMITED , FILEGROWTH = 65536KB )
GO

USE [EventsDB]
GO
/****** Object: Table [dbo].[Events] Script Date: 08.04.2019 00:47:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Events](
[EventUID] [int] IDENTITY(1,1) NOT NULL,
[Createdatetime] datetime2 NULL,
[EventtypeID] [int] NOT NULL,
[Executed] [bit] NOT NULL,
CONSTRAINT [PK_Events] PRIMARY KEY CLUSTERED
(
[EventUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Events] ON

INSERT [dbo].[Events] ([EventUID], [Createdatetime], [EventtypeID], [Executed]) VALUES (1, CAST(N'2019-04-01T00:08:09.8400000' AS DateTime2), 126, 0)
INSERT [dbo].[Events] ([EventUID], [Createdatetime], [EventtypeID], [Executed]) VALUES (2, CAST(N'2019-04-01T00:08:09.8400000' AS DateTime2), 126, 0)
INSERT [dbo].[Events] ([EventUID], [Createdatetime], [EventtypeID], [Executed]) VALUES (3, CAST(N'2019-04-01T00:08:09.8400000' AS DateTime2), 126, 0)
INSERT [dbo].[Events] ([EventUID], [Createdatetime], [EventtypeID], [Executed]) VALUES (4, CAST(N'2019-04-01T00:08:09.8400000' AS DateTime2), 126, 0)
INSERT [dbo].[Events] ([EventUID], [Createdatetime], [EventtypeID], [Executed]) VALUES (5, CAST(N'2019-04-01T00:08:09.8400000' AS DateTime2), 127, 0)
INSERT [dbo].[Events] ([EventUID], [Createdatetime], [EventtypeID], [Executed]) VALUES (6, CAST(N'2019-04-01T00:08:09.8400000' AS DateTime2), 127, 0)
INSERT [dbo].[Events] ([EventUID], [Createdatetime], [EventtypeID], [Executed]) VALUES (7, CAST(N'2019-04-01T00:08:09.8400000' AS DateTime2), 127, 0)
INSERT [dbo].[Events] ([EventUID], [Createdatetime], [EventtypeID], [Executed]) VALUES (8, CAST(N'2019-04-01T00:08:09.8400000' AS DateTime2), 126, 0)
INSERT [dbo].[Events] ([EventUID], [Createdatetime], [EventtypeID], [Executed]) VALUES (9, CAST(N'2019-04-01T00:08:09.8400000' AS DateTime2), 126, 0)
INSERT [dbo].[Events] ([EventUID], [Createdatetime], [EventtypeID], [Executed]) VALUES (10, CAST(N'2019-04-01T00:08:09.8400000' AS DateTime2), 126, 0)
INSERT [dbo].[Events] ([EventUID], [Createdatetime], [EventtypeID], [Executed]) VALUES (11, CAST(N'2019-04-01T00:08:09.8400000' AS DateTime2), 126, 0)
INSERT [dbo].[Events] ([EventUID], [Createdatetime], [EventtypeID], [Executed]) VALUES (12, CAST(N'2019-04-01T00:08:09.8400000' AS DateTime2), 127, 0)
INSERT [dbo].[Events] ([EventUID], [Createdatetime], [EventtypeID], [Executed]) VALUES (13, CAST(N'2019-04-01T00:08:09.8400000' AS DateTime2), 127, 0)
INSERT [dbo].[Events] ([EventUID], [Createdatetime], [EventtypeID], [Executed]) VALUES (14, CAST(N'2019-04-01T00:08:09.8400000' AS DateTime2), 126, 0)
INSERT [dbo].[Events] ([EventUID], [Createdatetime], [EventtypeID], [Executed]) VALUES (15, CAST(N'2019-04-01T00:08:09.8400000' AS DateTime2), 126, 0)
INSERT [dbo].[Events] ([EventUID], [Createdatetime], [EventtypeID], [Executed]) VALUES (16, CAST(N'2019-04-01T00:08:09.8400000' AS DateTime2), 126, 0)

GO
SET IDENTITY_INSERT [dbo].[Events] OFF
GO
ALTER TABLE [dbo].[Events] ADD CONSTRAINT [DF_Events_Executed] DEFAULT ((0)) FOR [Executed]
GO

Reiner · April 8, 2019, 10:51pm

Now the Problem comes for me:

Select * from Events
Where Executed = 0
Order By EventUID

|1|2019-04-01 00:08:09.8400000|126|0|
|2|2019-04-01 00:08:09.8400000|126|0|
|3|2019-04-01 00:08:09.8400000|126|0|
|4|2019-04-01 00:08:09.8400000|126|0|
|5|2019-04-01 00:08:09.8400000|127|0|
|6|2019-04-01 00:08:09.8400000|127|0|
|7|2019-04-01 00:08:09.8400000|127|0|
|8|2019-04-01 00:08:09.8400000|126|0|
|9|2019-04-01 00:08:09.8400000|126|0|
|10|2019-04-01 00:08:09.8400000|126|0|
|11|2019-04-01 00:08:09.8400000|126|0|
|12|2019-04-01 00:08:09.8400000|127|0|
|13|2019-04-01 00:08:09.8400000|127|0|
|14|2019-04-01 00:08:09.8400000|126|0|
|15|2019-04-01 00:08:09.8400000|126|0|
|16|2019-04-01 00:08:09.8400000|126|0|

The Question is now:

I Need in one Query to resolve maybe two Eventtypes. But i need only one by one Eventtype. I must handle both Eventypes in this Querry.

The 126 Eventtype Recordset what is not executed Order By EventUID but breaked by an 127 Eventype what is not executed.
or
the 127 Eventype what is not executed Order By EventUID but breaked by a 126 Eventype what is not executed.

Order by EventUID i need to hold in the Timeline Sequence of the Events.

If the 126 Eventtype is breaked by a 127 Eventype, i must send it beetween maybe two 126 Eventype Recordsets.

Because the 126 Eventypes must be handled different as the 127 Eventtype. But it must be in Sequence of Time. So i must handle in Sequence.

After i read the Eventrecordset i set Executed to True

Abstract written i need something like this:

SELECT * from Events

WHERE EventtypeID = 126 AND Executed = 0
BREAKED by EventtypeID = 127

**OR **

WHERE EventtypeID = 127 AND Executed = 0
BREAKED by EventtypeID = 126

ORDER BY EventUID

Hope this helps to explain my Problem a bit.

Best Regards
Reiner

Ifor · April 10, 2019, 2:11pm

Maybe:

WITH EventGrps
AS
(
	SELECT EventTypeId, EventUID, Createdatetime
		,ROW_NUMBER() OVER (ORDER BY EventUID) 
			- ROW_NUMBER() OVER (PARTITION BY EventTypeId ORDER BY EventUID) AS Grp
	FROM dbo.[Events]
	WHERE Executed = 0
)
,Results
AS
(
	SELECT EventTypeId, Grp
		,MIN(EventUID) AS MinEventUID
		,MAX(EventUID) AS MaxEventUID
		,MIN(Createdatetime) AS MinCreatedatetime
		,MAX(Createdatetime) AS MaxCreatedatetime
	FROM EventGrps
	GROUP BY EventTypeId, Grp
)
SELECT EventTypeId
	,MinEventUID, MaxEventUID
	,MinCreatedatetime, MaxCreatedatetime
FROM Results
ORDER BY MinEventUID;

Reiner · April 11, 2019, 11:57pm

Wow.

Great Query.Thx allot.

I produces this by myself:

SELECT EventtypeID, StartUID=MIN(EventUID), EndUID=MAX(EventUID)
FROM (
SELECT EventtypeID, EventUID, RNr = EventUID-ROW_NUMBER() OVER (PARTITION BY EventtypeID ORDER BY EventUID)
FROM [EventsDB].[dbo].[Events] WHERE Executed = 0) D
GROUP BY EventtypeID, RNr
ORDER BY StartUID
GO

Please let us discusse about Impact.

Best Regards

Reiner

I edit the Post, because i post not the right querry!

Reiner · April 11, 2019, 11:58pm

And i will check Performance about your Querry.

Best Regards

Reiner

Reiner · April 12, 2019, 12:02am

But yours looks better. I trie to understand.

Ifor · April 12, 2019, 11:12am

This looks like and Islands and Gaps problem:

eg https://www.red-gate.com/simple-talk/sql/t-sql-programming/the-sql-of-gaps-and-islands-in-sequences/

Reiner · April 12, 2019, 1:15pm

I edit the upper Post, because i post not the right querry!

And you are right Ifor. It looks like Island and Gaps problem. Thx for the Link.

best Regards

Reiner