Reader Challenge Part III - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Reader Challenge Part III

Author

Topic

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-12 : 19:48:19

Since Jeff and Corey quickly polished off my last 2 challenges, here is one final puzzler. (I'm sure there
are many others out there, but these three are questions I have run across and found to be difficult in the
past few months.)

The setup:
----------------------------------------
I have a log file that takes readings of events at periodic intervals which can be as little as every 10 seconds.
Sometimes it can be as much as 10 minutes apart though, depending on what is going on...

I have a small report I run on this file, that effectively does:

SELECT *
FROM EventFile
WHERE deviceID=x AND groupID=y
ORDER BY eventDateTime asc

The problem with this, is that as there could be a lot of events for a particular device & group,
I only want to show the readout on my asp-driven report where the events are a minute apart.
In the worst-case scenario, I could end up dropping 5 records out of every 6 I've read in, and this
strikes me as super-inefficient... why select rows I'm not going to use?

I need a way of selecting rows where the eventdatetime is at least a minute apart from the previously-selected row.

----------------------------------------
Some DDL:


CREATE TABLE [dbo].[eventFile] (
 [eventid] [int] NULL ,
 [deviceid] [int] NULL ,
 [groupid] [int] NULL ,
 [groupdatetime] [datetime] NULL 
) ON [PRIMARY]

INSERT eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472704 ,18 ,3 ,'2004-07-02 16:15:09.000')
INSERT eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472708 ,18 ,3 ,'2004-07-02 16:15:59.000')
INSERT eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472737 ,18 ,3 ,'2004-07-02 16:20:14.000')
INSERT eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472741 ,18 ,3 ,'2004-07-02 16:20:50.000')
INSERT eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472745 ,18 ,3 ,'2004-07-02 16:21:00.000')
INSERT eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472746 ,18 ,3 ,'2004-07-02 16:21:22.000')
INSERT eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472750 ,18 ,3 ,'2004-07-02 16:21:32.000')
INSERT eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472751 ,18 ,3 ,'2004-07-02 16:21:42.000')
INSERT eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472755 ,18 ,3 ,'2004-07-02 16:22:03.000')
INSERT eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472756 ,18 ,3 ,'2004-07-02 16:22:13.000')
INSERT eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472760 ,18 ,3 ,'2004-07-02 16:22:32.000')
INSERT eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472761 ,18 ,3 ,'2004-07-02 16:22:50.000')
INSERT eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472766 ,18 ,3 ,'2004-07-02 16:23:00.000')
INSERT eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472767 ,18 ,3 ,'2004-07-02 16:23:10.000')
INSERT eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472771 ,18 ,3 ,'2004-07-02 16:23:27.000')
INSERT eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472772 ,18 ,3 ,'2004-07-02 16:23:42.000')
INSERT eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472773 ,18 ,3 ,'2004-07-02 16:23:52.000')
INSERT eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472777 ,18 ,3 ,'2004-07-02 16:24:02.000')
INSERT eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472781 ,18 ,3 ,'2004-07-02 16:24:34.000')
INSERT eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472782 ,18 ,3 ,'2004-07-02 16:24:44.000')
INSERT eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472786 ,18 ,3 ,'2004-07-02 16:24:54.000')
INSERT eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472787 ,18 ,3 ,'2004-07-02 16:25:04.000')
INSERT eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472791 ,18 ,3 ,'2004-07-02 16:25:44.000')
INSERT eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472795 ,18 ,3 ,'2004-07-02 16:25:54.000')
INSERT eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472796 ,18 ,3 ,'2004-07-02 16:26:04.000')
INSERT eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472797 ,18 ,3 ,'2004-07-02 16:26:14.000')
INSERT eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472801 ,18 ,3 ,'2004-07-02 16:26:24.000')
INSERT eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472802 ,18 ,3 ,'2004-07-02 16:26:34.000')
INSERT eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472803 ,18 ,3 ,'2004-07-02 16:26:44.000')
INSERT eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472807 ,18 ,3 ,'2004-07-02 16:26:54.000')
INSERT eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472808 ,18 ,3 ,'2004-07-02 16:27:04.000')
INSERT eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472809 ,18 ,3 ,'2004-07-02 16:27:14.000')
INSERT eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472814 ,18 ,3 ,'2004-07-02 16:27:24.000')
INSERT eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472815 ,18 ,3 ,'2004-07-02 16:27:34.000')
INSERT eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472816 ,18 ,3 ,'2004-07-02 16:27:44.000')
INSERT eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472821 ,18 ,3 ,'2004-07-02 16:27:59.000')
INSERT eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472822 ,18 ,3 ,'2004-07-02 16:28:09.000')
INSERT eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472823 ,18 ,3 ,'2004-07-02 16:28:19.000')
INSERT eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472827 ,18 ,3 ,'2004-07-02 16:28:29.000')
INSERT eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472831 ,18 ,3 ,'2004-07-02 16:29:02.000')
INSERT eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472836 ,18 ,3 ,'2004-07-02 16:29:30.000')
INSERT eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472837 ,18 ,3 ,'2004-07-02 16:29:40.000')
INSERT eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472838 ,18 ,3 ,'2004-07-02 16:29:50.000')
INSERT eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472843 ,18 ,3 ,'2004-07-02 16:30:00.000')
INSERT eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472844 ,18 ,3 ,'2004-07-02 16:30:10.000')

Cheers,

--Ken
Your Kung-Fu is not strong. -- 'The Core'

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-12 : 22:25:10

Alright... I think I've got this one. It isn't quite as nice as the last one I did, and I'm going to continue to think about it, but here it is...


CREATE TABLE #eventFile
(
 [eventid] int NULL ,
 [deviceid] int NULL ,
 [groupid] int NULL ,
 [groupdatetime] datetime NULL 
)

INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472704 ,18 ,3 ,'2004-07-02 16:15:09.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472708 ,18 ,3 ,'2004-07-02 16:15:59.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472737 ,18 ,3 ,'2004-07-02 16:20:14.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472741 ,18 ,3 ,'2004-07-02 16:20:50.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472745 ,18 ,3 ,'2004-07-02 16:21:00.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472746 ,18 ,3 ,'2004-07-02 16:21:22.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472750 ,18 ,3 ,'2004-07-02 16:21:32.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472751 ,18 ,3 ,'2004-07-02 16:21:42.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472755 ,18 ,3 ,'2004-07-02 16:22:03.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472756 ,18 ,3 ,'2004-07-02 16:22:13.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472760 ,18 ,3 ,'2004-07-02 16:22:32.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472761 ,18 ,3 ,'2004-07-02 16:22:50.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472766 ,18 ,3 ,'2004-07-02 16:23:00.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472767 ,18 ,3 ,'2004-07-02 16:23:10.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472771 ,18 ,3 ,'2004-07-02 16:23:27.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472772 ,18 ,3 ,'2004-07-02 16:23:42.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472773 ,18 ,3 ,'2004-07-02 16:23:52.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472777 ,18 ,3 ,'2004-07-02 16:24:02.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472781 ,18 ,3 ,'2004-07-02 16:24:34.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472782 ,18 ,3 ,'2004-07-02 16:24:44.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472786 ,18 ,3 ,'2004-07-02 16:24:54.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472787 ,18 ,3 ,'2004-07-02 16:25:04.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472791 ,18 ,3 ,'2004-07-02 16:25:44.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472795 ,18 ,3 ,'2004-07-02 16:25:54.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472796 ,18 ,3 ,'2004-07-02 16:26:04.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472797 ,18 ,3 ,'2004-07-02 16:26:14.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472801 ,18 ,3 ,'2004-07-02 16:26:24.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472802 ,18 ,3 ,'2004-07-02 16:26:34.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472803 ,18 ,3 ,'2004-07-02 16:26:44.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472807 ,18 ,3 ,'2004-07-02 16:26:54.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472808 ,18 ,3 ,'2004-07-02 16:27:04.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472809 ,18 ,3 ,'2004-07-02 16:27:14.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472814 ,18 ,3 ,'2004-07-02 16:27:24.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472815 ,18 ,3 ,'2004-07-02 16:27:34.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472816 ,18 ,3 ,'2004-07-02 16:27:44.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472821 ,18 ,3 ,'2004-07-02 16:27:59.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472822 ,18 ,3 ,'2004-07-02 16:28:09.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472823 ,18 ,3 ,'2004-07-02 16:28:19.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472827 ,18 ,3 ,'2004-07-02 16:28:29.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472831 ,18 ,3 ,'2004-07-02 16:29:02.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472836 ,18 ,3 ,'2004-07-02 16:29:30.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472837 ,18 ,3 ,'2004-07-02 16:29:40.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472838 ,18 ,3 ,'2004-07-02 16:29:50.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472843 ,18 ,3 ,'2004-07-02 16:30:00.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472844 ,18 ,3 ,'2004-07-02 16:30:10.000')

Declare @x int,
	@y int,
	@lastCnt int,
	@counter int

Declare @nextEvents1 table (eventId int, deviceId int, groupId int, groupDatetime datetime, nextEventId int)
Declare @nextEvents2 table (eventId int, deviceId int, groupId int, groupDatetime datetime, nextEventId int)

Set @x = 18
Set @y = 3

Select * From #eventFile

Insert Into @nextEvents1
SELECT 
	eventId, 
	deviceId, 
	groupId,
	groupDatetime,
	nextEventId = (Select min(eventId) From #EventFile Where groupDatetime > dateadd(mi,1,A.groupDatetime))
FROM #EventFile as A
WHERE deviceID=@x AND groupID=@y
ORDER BY groupDateTime asc

Set @lastCnt = (Select count(*) From @nextEvents1)
set @counter = 1

While (@lastCnt>0 and @counter < 100)
Begin
	Insert Into @nextEvents2
	Select Distinct
		A.* 
	From @nextEvents1 as A
	Inner Join (Select nextEventId=min(EventId) From @nextEvents1 Union Select nextEventId From @nextEvents1) as B
	On A.eventId = B.nextEventId

	Delete From @nextEvents1	
	
	Insert Into @nextEvents1
	Select A.* 
	From @nextEvents2 as A 
	Inner Join (Select nextEventId=min(EventId) From @nextEvents2 Union Select nextEventId From @nextEvents2) as B
	On A.eventId = B.nextEventId

	Delete From @nextEvents2

	if (@lastCnt<>(Select count(*) From @nextEvents1))
	Begin
		Select @lastCnt, (Select count(*) From @nextEvents1)
		Set @lastCnt = (Select count(*) From @nextEvents1)
	End
	else
	Begin
		Set @lastCnt = 0
	End
	set @counter = @counter + 1
End

Select * From @nextEvents1
Select @counter

Drop Table #eventFile

Corey

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-12 : 22:27:27

You can do better than that!

And kudos on your solution to Part II. Very nice

--Ken
Your Kung-Fu is not strong. -- 'The Core'

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-12 : 22:29:16

ill think about it tonight... it works though

Corey

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-13 : 00:19:25

Ken - here's my next shot. A little smaller, a little faster...


CREATE TABLE #eventFile
(
 [eventid] int NULL ,
 [deviceid] int NULL ,
 [groupid] int NULL ,
 [groupdatetime] datetime NULL 
)

INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472704 ,18 ,3 ,'2004-07-02 16:15:09.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472708 ,18 ,3 ,'2004-07-02 16:15:59.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472737 ,18 ,3 ,'2004-07-02 16:20:14.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472741 ,18 ,3 ,'2004-07-02 16:20:50.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472745 ,18 ,3 ,'2004-07-02 16:21:00.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472746 ,18 ,3 ,'2004-07-02 16:21:22.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472750 ,18 ,3 ,'2004-07-02 16:21:32.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472751 ,18 ,3 ,'2004-07-02 16:21:42.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472755 ,18 ,3 ,'2004-07-02 16:22:03.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472756 ,18 ,3 ,'2004-07-02 16:22:13.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472760 ,18 ,3 ,'2004-07-02 16:22:32.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472761 ,18 ,3 ,'2004-07-02 16:22:50.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472766 ,18 ,3 ,'2004-07-02 16:23:00.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472767 ,18 ,3 ,'2004-07-02 16:23:10.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472771 ,18 ,3 ,'2004-07-02 16:23:27.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472772 ,18 ,3 ,'2004-07-02 16:23:42.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472773 ,18 ,3 ,'2004-07-02 16:23:52.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472777 ,18 ,3 ,'2004-07-02 16:24:02.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472781 ,18 ,3 ,'2004-07-02 16:24:34.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472782 ,18 ,3 ,'2004-07-02 16:24:44.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472786 ,18 ,3 ,'2004-07-02 16:24:54.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472787 ,18 ,3 ,'2004-07-02 16:25:04.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472791 ,18 ,3 ,'2004-07-02 16:25:44.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472795 ,18 ,3 ,'2004-07-02 16:25:54.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472796 ,18 ,3 ,'2004-07-02 16:26:04.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472797 ,18 ,3 ,'2004-07-02 16:26:14.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472801 ,18 ,3 ,'2004-07-02 16:26:24.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472802 ,18 ,3 ,'2004-07-02 16:26:34.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472803 ,18 ,3 ,'2004-07-02 16:26:44.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472807 ,18 ,3 ,'2004-07-02 16:26:54.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472808 ,18 ,3 ,'2004-07-02 16:27:04.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472809 ,18 ,3 ,'2004-07-02 16:27:14.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472814 ,18 ,3 ,'2004-07-02 16:27:24.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472815 ,18 ,3 ,'2004-07-02 16:27:34.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472816 ,18 ,3 ,'2004-07-02 16:27:44.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472821 ,18 ,3 ,'2004-07-02 16:27:59.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472822 ,18 ,3 ,'2004-07-02 16:28:09.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472823 ,18 ,3 ,'2004-07-02 16:28:19.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472827 ,18 ,3 ,'2004-07-02 16:28:29.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472831 ,18 ,3 ,'2004-07-02 16:29:02.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472836 ,18 ,3 ,'2004-07-02 16:29:30.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472837 ,18 ,3 ,'2004-07-02 16:29:40.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472838 ,18 ,3 ,'2004-07-02 16:29:50.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472843 ,18 ,3 ,'2004-07-02 16:30:00.000')
INSERT #eventfile(eventid,deviceid,groupid,groupdatetime) VALUES(472844 ,18 ,3 ,'2004-07-02 16:30:10.000')

Declare @x int,
	@y int

Declare @nextEvents table (eventId int, deviceId int, groupId int, groupDatetime datetime, nextEventId int)

Set @x = 18
Set @y = 3

Select * From #eventFile

Insert Into @nextEvents
SELECT 
	eventId, 
	deviceId, 
	groupId,
	groupDatetime,
	nextEventId = (Select min(eventId) From #EventFile Where groupDatetime > dateadd(mi,1,A.groupDatetime))
FROM #EventFile as A
WHERE deviceID=@x AND groupID=@y
ORDER BY groupDateTime asc

Declare @eventList nvarchar(4000)

Set @eventList = ',' + convert(nvarchar,(Select min(eventId) From @nextEvents))
Select @eventList = @eventList + (case when convert(nvarchar,eventId) = right(@eventList,charindex(',',reverse(@eventList))-1) then ',' + isnull(convert(nvarchar,nextEventId),'') else '' end) From @nextEvents

Select * From @nextEvents Where @eventList like ('%' + convert(nvarchar,eventId) + '%')

Drop Table #eventFile

Do you approve??

Corey

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-13 : 00:21:58

Much better. Change > dateadd to >= dateadd because 1 minute difference is acceptable.

You da man!
Now I'm going to have to figure out how that works

Should I have mentioned the table has 400,000 rows?

--Ken
Your Kung-Fu is not strong. -- 'The Core'

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-13 : 07:24:30

yeah 400,000 rows might be a few too many for the second solution. The first solution would still work though. The second solution will only be able to handle about 570 events on a given report... (just shy of 10 hours guaranteed)

Shall I keep thinking??

Corey

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-13 : 14:23:52

I think this is done enough unless someone can do it without loops. I had come up with this:

SELECT eventid,( 
    SELECT top 1 eventid
    FROM eventfile 
    WHERE o.groupid = groupid 
      AND o.deviceid = deviceid 
      AND groupdatetime > o.groupdatetime 
      AND datediff(s,o.groupdatetime,groupdatetime) >= 60
    ) AS nextevent
 INTO #temp
 FROM eventfile o 
 WHERE deviceid = 18 AND groupid = 3
 ORDER BY eventid 
 
 DECLARE @eid INT
 SELECT @eid = min(eventid) FROM #temp
 DECLARE @walk TABLE (eid INT)
 WHILE @eid IS NOT NULL
 BEGIN
    INSERT @walk SELECT @eid
    SELECT @eid = nextevent FROM #temp WHERE @eid = eventid
 END
 
 SELECT * FROM eventfile, @walk w WHERE eventfile.eventid = w.eid

--Ken
Your Kung-Fu is not strong. -- 'The Core'

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-09-05 : 16:46:46

Figured I throw this in mostly to point spirit1 to one of my more extravagent "undocumented" forays. My initial attempt to do this puzzle without a loop a few months ago ended up with this;


DECLARE @a INT
SET @a = dbo.EventList(null,null,null)

SELECT e.* FROM eventfile e,
(
SELECT top 100 percent eventid, dbo.EventList(@a, eventid, 
   (SELECT top 1 eventid nextevent
    FROM eventfile 
    WHERE o.groupid = groupid AND o.deviceid = deviceid AND groupdatetime > o.groupdatetime AND datediff(s,o.groupdatetime,groupdatetime) >= 60
    )) nextevent
FROM eventfile o 
WHERE deviceid = 540 AND groupid = 8
ORDER BY eventid 
) etree
WHERE e.eventid = etree.eventid and etree.nextevent is not null
ORDER BY e.eventid

What is dbo.Eventlist() above? An obscene manipulation of non-deterministic UDF limitations.



CREATE ALTER FUNCTION dbo.EventList(@obj integer, @event int, @nextevent int)
RETURNS int
AS
BEGIN
DECLARE @cevent varchar(20)
DECLARE @cnextevent varchar(20)
DECLARE @count int
DECLARE @Newobj int
DECLARE @exists bit
IF @obj IS NULL
BEGIN
 EXECUTE sp_OACreate 'Scripting.Dictionary', @Newobj OUTPUT
 SET @nextevent = @Newobj
END
ELSE
BEGIN
        SELECT @cnextevent = convert(varchar(20),@nextevent)
        SELECT @cevent = convert(varchar(20),@event)
 EXECUTE sp_OAMethod @obj, 'Count', @count OUT
 If @count = 0
 BEGIN
  EXECUTE sp_OAMethod @obj, 'ADD', NULL, @cevent, @cevent
         EXECUTE sp_OAMethod @obj, 'ADD', NULL, @cnextevent, @cnextevent
 END
 ELSE
 BEGIN
  EXECUTE sp_OAMethod @obj, 'Exists', @exists OUT, @cevent
  IF @exists = 1 
   EXECUTE sp_OAMethod @obj, 'ADD', NULL, @cnextevent, @cnextevent
  ELSE
   SET @nextevent = NULL
 END
END
RETURN @nextevent
END

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-05 : 19:28:54

I think I have another solution... with no non-deterministic functions... The part in bold is the important part.

Let me know what you think!


/*
--Drop Function dbo.nextEventId
CREATE FUNCTION dbo.nextEventId
(
	@lastEventId int,
	@curEventId int
)  
RETURNS int AS  
BEGIN 
	Declare @rtnVal int,
		@lastEvent datetime

	if (@lastEventId<>@curEventId)
	Begin
		return @lastEventId
	End

	Set @lastEvent = (Select groupDatetime From nextEvents Where eventId = @lastEventId)
	Set @rtnVal = (Select min(eventId) From nextEvents Where groupDatetime > dateadd(mi,1,@lastEvent))

	Return @rtnVal
END
*/


CREATE TABLE eventFile
(
 [eventid] int NULL ,
 [deviceid] int NULL ,
 [groupid] int NULL ,
 [groupdatetime] datetime NULL 
)

INSERT eventFile(eventid,deviceid,groupid,groupdatetime) VALUES(472704 ,18 ,3 ,'2004-07-02 16:15:09.000')
INSERT eventFile(eventid,deviceid,groupid,groupdatetime) VALUES(472708 ,18 ,3 ,'2004-07-02 16:15:59.000')
INSERT eventFile(eventid,deviceid,groupid,groupdatetime) VALUES(472737 ,18 ,3 ,'2004-07-02 16:20:14.000')
INSERT eventFile(eventid,deviceid,groupid,groupdatetime) VALUES(472741 ,18 ,3 ,'2004-07-02 16:20:50.000')
INSERT eventFile(eventid,deviceid,groupid,groupdatetime) VALUES(472745 ,18 ,3 ,'2004-07-02 16:21:00.000')
INSERT eventFile(eventid,deviceid,groupid,groupdatetime) VALUES(472746 ,18 ,3 ,'2004-07-02 16:21:22.000')
INSERT eventFile(eventid,deviceid,groupid,groupdatetime) VALUES(472750 ,18 ,3 ,'2004-07-02 16:21:32.000')
INSERT eventFile(eventid,deviceid,groupid,groupdatetime) VALUES(472751 ,18 ,3 ,'2004-07-02 16:21:42.000')
INSERT eventFile(eventid,deviceid,groupid,groupdatetime) VALUES(472755 ,18 ,3 ,'2004-07-02 16:22:03.000')
INSERT eventFile(eventid,deviceid,groupid,groupdatetime) VALUES(472756 ,18 ,3 ,'2004-07-02 16:22:13.000')
INSERT eventFile(eventid,deviceid,groupid,groupdatetime) VALUES(472760 ,18 ,3 ,'2004-07-02 16:22:32.000')
INSERT eventFile(eventid,deviceid,groupid,groupdatetime) VALUES(472761 ,18 ,3 ,'2004-07-02 16:22:50.000')
INSERT eventFile(eventid,deviceid,groupid,groupdatetime) VALUES(472766 ,18 ,3 ,'2004-07-02 16:23:00.000')
INSERT eventFile(eventid,deviceid,groupid,groupdatetime) VALUES(472767 ,18 ,3 ,'2004-07-02 16:23:10.000')
INSERT eventFile(eventid,deviceid,groupid,groupdatetime) VALUES(472771 ,18 ,3 ,'2004-07-02 16:23:27.000')
INSERT eventFile(eventid,deviceid,groupid,groupdatetime) VALUES(472772 ,18 ,3 ,'2004-07-02 16:23:42.000')
INSERT eventFile(eventid,deviceid,groupid,groupdatetime) VALUES(472773 ,18 ,3 ,'2004-07-02 16:23:52.000')
INSERT eventFile(eventid,deviceid,groupid,groupdatetime) VALUES(472777 ,18 ,3 ,'2004-07-02 16:24:02.000')
INSERT eventFile(eventid,deviceid,groupid,groupdatetime) VALUES(472781 ,18 ,3 ,'2004-07-02 16:24:34.000')
INSERT eventFile(eventid,deviceid,groupid,groupdatetime) VALUES(472782 ,18 ,3 ,'2004-07-02 16:24:44.000')
INSERT eventFile(eventid,deviceid,groupid,groupdatetime) VALUES(472786 ,18 ,3 ,'2004-07-02 16:24:54.000')
INSERT eventFile(eventid,deviceid,groupid,groupdatetime) VALUES(472787 ,18 ,3 ,'2004-07-02 16:25:04.000')
INSERT eventFile(eventid,deviceid,groupid,groupdatetime) VALUES(472791 ,18 ,3 ,'2004-07-02 16:25:44.000')
INSERT eventFile(eventid,deviceid,groupid,groupdatetime) VALUES(472795 ,18 ,3 ,'2004-07-02 16:25:54.000')
INSERT eventFile(eventid,deviceid,groupid,groupdatetime) VALUES(472796 ,18 ,3 ,'2004-07-02 16:26:04.000')
INSERT eventFile(eventid,deviceid,groupid,groupdatetime) VALUES(472797 ,18 ,3 ,'2004-07-02 16:26:14.000')
INSERT eventFile(eventid,deviceid,groupid,groupdatetime) VALUES(472801 ,18 ,3 ,'2004-07-02 16:26:24.000')
INSERT eventFile(eventid,deviceid,groupid,groupdatetime) VALUES(472802 ,18 ,3 ,'2004-07-02 16:26:34.000')
INSERT eventFile(eventid,deviceid,groupid,groupdatetime) VALUES(472803 ,18 ,3 ,'2004-07-02 16:26:44.000')
INSERT eventFile(eventid,deviceid,groupid,groupdatetime) VALUES(472807 ,18 ,3 ,'2004-07-02 16:26:54.000')
INSERT eventFile(eventid,deviceid,groupid,groupdatetime) VALUES(472808 ,18 ,3 ,'2004-07-02 16:27:04.000')
INSERT eventFile(eventid,deviceid,groupid,groupdatetime) VALUES(472809 ,18 ,3 ,'2004-07-02 16:27:14.000')
INSERT eventFile(eventid,deviceid,groupid,groupdatetime) VALUES(472814 ,18 ,3 ,'2004-07-02 16:27:24.000')
INSERT eventFile(eventid,deviceid,groupid,groupdatetime) VALUES(472815 ,18 ,3 ,'2004-07-02 16:27:34.000')
INSERT eventFile(eventid,deviceid,groupid,groupdatetime) VALUES(472816 ,18 ,3 ,'2004-07-02 16:27:44.000')
INSERT eventFile(eventid,deviceid,groupid,groupdatetime) VALUES(472821 ,18 ,3 ,'2004-07-02 16:27:59.000')
INSERT eventFile(eventid,deviceid,groupid,groupdatetime) VALUES(472822 ,18 ,3 ,'2004-07-02 16:28:09.000')
INSERT eventFile(eventid,deviceid,groupid,groupdatetime) VALUES(472823 ,18 ,3 ,'2004-07-02 16:28:19.000')
INSERT eventFile(eventid,deviceid,groupid,groupdatetime) VALUES(472827 ,18 ,3 ,'2004-07-02 16:28:29.000')
INSERT eventFile(eventid,deviceid,groupid,groupdatetime) VALUES(472831 ,18 ,3 ,'2004-07-02 16:29:02.000')
INSERT eventFile(eventid,deviceid,groupid,groupdatetime) VALUES(472836 ,18 ,3 ,'2004-07-02 16:29:30.000')
INSERT eventFile(eventid,deviceid,groupid,groupdatetime) VALUES(472837 ,18 ,3 ,'2004-07-02 16:29:40.000')
INSERT eventFile(eventid,deviceid,groupid,groupdatetime) VALUES(472838 ,18 ,3 ,'2004-07-02 16:29:50.000')
INSERT eventFile(eventid,deviceid,groupid,groupdatetime) VALUES(472843 ,18 ,3 ,'2004-07-02 16:30:00.000')
INSERT eventFile(eventid,deviceid,groupid,groupdatetime) VALUES(472844 ,18 ,3 ,'2004-07-02 16:30:10.000')

	Declare @x int,
		@y int,
		@lastEvent datetime,
		@nextEventId int
	Select @x=18, @y=3

	Create Table nextEvents (eventId int, deviceId int, groupId int, groupDatetime datetime, nextEventId int default(0))

	Insert Into nextEvents
	SELECT 
		eventId, 
		deviceId, 
		groupId,
		groupDatetime,
		nextEventId = 0
	FROM eventFile as A
	WHERE deviceID=@x AND groupID=@y
	ORDER BY groupDateTime asc

	Set @nextEventId = (Select min(eventId) From eventFile Where deviceID=@x AND groupID=@y)

	Update A
	Set 
		@nextEventId = nextEventId = dbo.nextEventId	(@nextEventId,eventId)
	FROM nextEvents as A
	WHERE deviceID=@x AND groupID=@y

	Select A.* 
	From nextEvents as A
	Inner Join 
		(Select eventId = min(eventId) From nextEvents Where 	nextEventId is not null Group By nextEventId) as B
	On A.eventId = B.eventId
	Order By groupDatetime

Drop Table nextEvents
Drop Table eventFile

Corey

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-09-05 : 20:34:13

I like the sneaky way you did it the first time actually :) I wonder how your last solution would handle 400,000 rows. My funky UDF takes about 30 seconds on 80,000 rows. I'll run your last solution through it and let you know.

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-11 : 15:03:02

Ken - Have you had a chance to test out the last solution?? No rush, I'm just curious about how it would perform...

Corey

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-09-11 : 16:43:42

Quad 1GHZ Xeon 2GB RAM

EvenfFile: 92,000 rows
DeviceID 540, Group Id 8: 16,000 rows
Processing Time: 1 minute 57 seconds.
4774 rows output

BTW: I changed > dateadd(mi,1,@lastEvent)) to >= dateadd(mi,1,@lastEvent))

My solution using the @walk table generated 4775 rows output in 35 seconds.
My version using the Scripting.Dictionary generated 4774 rows in 39 seconds.
(Edit(2): Not sure which row is different but I guess 4774 is the right answer

)
You can generate sample data like I did by downloading LogParser 2.0 from microsoft at

http://www.microsoft.com/downloads/details.aspx?displaylang=en&familyid=8cde4028-e247-45be-bab9-ac851fc166a4

Install it on the machine running SQL Server and run:

C:\Program Files\Log Parser>logparser "SELECT EventLog, RecordNumber, TimeGenerated, EventID, EventType, EventTypeName, EventCategory, SourceName, ComputerName, Message, TO_STRING(SUB(TO_LOCALTIME(SYSTEM_TIMESTAMP()), TimeGenerated), 'MM-dd-yyyy') AS Delta FROM System, Application, Security TO tblEvents" -o:SQL -server:<yourserver> -driver:"SQL Server" -database:<yourdb> -username:sa -password:<sapwd> -createtable:ON


CREATE TABLE eventFile (eventid int, deviceid int, groupid int, groupdatetime datetime)

INSERT eventfile 
SELECT recordnumber, eventid, eventtype, timegenerated
FROM tblEvents

CREATE CLUSTERED INDEX ev_date_idx on eventfile (groupdatetime)
CREATE INDEX ev_dg_idx on eventfile (deviceid, groupid)

--To select Device,Group pick group with higher count from this list:
SELECT COUNT(1), deviceid, groupid FROM eventfile GROUP BY groupid, deviceid
ORDER BY COUNT (1)

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-12 : 00:42:36

What was the @walk table?

I tested it out on my server, but I don't have that much activity at home so it wasn't a great test...

Thanks for the tipon the log parser though

Corey

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-09-12 : 06:05:33

quote:
Originally posted by Seventhnight

What was the @walk table?

I tested it out on my server, but I don't have that much activity at home so it wasn't a great test...

Thanks for the tipon the log parser though

Corey

Search this post for @walk, it was my first solution.

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-12 : 11:07:26

ahh... ok

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources