Tagging an event - 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

General SQL Server Forums

New to SQL Server Programming

Tagging an event

Author

Topic

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2008-03-08 : 16:18:14

If I want to find other events that occur within a specified time frame of one event, what would the logic be?

Please consider the following:

2 tables:

Booking B
Events E

Fields:
b.bookingid
e.reqtime

I want to find all B.Bookingid that have an e.reqtime +/ 15 minutes from eachother.

I currently use the following query to find all b.bookingid with the same e.reqtime...it would be helpful to find other bookings within a 15 minute window of eachother.

Currently use:

SELECT e.reqtime, count(b.bookingid) as 'Trips'

From Booking b
JOIN events e
ON e.bookingid=b.bookingid
and e.activity=0

Where b.ldate=?

Group by e.reqtime

having count(e.reqtime)>=2

Order by e.reqtime

Thanks Much!

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-08 : 17:02:44

Here is sample code and an answer to demonstrate



Create Table #bookings (BookingID int identity (1,1),Textcolumn char(1) not null)

Create Table #events (BookingID int not null, reqtime datetime not null)

Insert INTO #bookings (textcolumn)

Select  'A'
From master..spt_values
Where type = 'P' and number between 1 and 10

Insert into #events (bookingid,reqtime)
Select 1,'1/1/2008 1:00:00.000 PM' UNION ALL
Select 2,'1/1/2008 12:45:00.000 PM' UNION ALL
Select 3,'1/1/2008 2:15:00.000 PM' UNION ALL
Select 4,'1/1/2008 7:25:00.000 PM' UNION ALL
Select 5,'1/1/2008 7:10:00.000 PM' UNION ALL
Select 6,'1/1/2008 8:00:00.000 PM' UNION ALL
Select 7,'1/1/2008 8:30:00.000 PM' UNION ALL
Select 8,'1/1/2008 2:45:00.000 PM' UNION ALL
Select 9,'1/1/2008 3:00:00.000 PM' UNION ALL
Select 10,'1/1/2008 2:00:00.000 PM' 

--SQL 2000
Select identity(int, 1,1) as RowNum,
							BookingId,
							reqtime
INTO #orderedevents
FROM #events
Order by reqtime asc

Select e1.BookingID, e1.ReqTime,e2.BookingID as Conflict,e2.ReqTime as ConflictTime
From #orderedevents e1 ,#orderedevents e2 
Where ABS(datediff(mi,e1.ReqTime,e2.ReqTime)) <=15  AND e1.BookingID != e2.BookingID
order by BookingID

--SQL 2005

;WITH cteOrdered AS
(
 SELECT ROW_NUMBER() OVER (ORDER BY ReqTime desc) AS RowNum,
        BookingID,
        ReqTime
   FROM #events
)

Select e1.BookingID, e1.ReqTime,e2.BookingID as Conflict,e2.ReqTime as ConflictTime
From cteOrdered e1 ,cteOrdered e2 
Where ABS(datediff(mi,e1.ReqTime,e2.ReqTime)) <=15  AND e1.BookingID != e2.BookingID
order by BookingID


drop table #bookings
drop table #events
drop table #orderedevents

/*output from both is identical
1	2008-01-01 13:00:00.000	2	2008-01-01 12:45:00.000
2	2008-01-01 12:45:00.000	1	2008-01-01 13:00:00.000
3	2008-01-01 14:15:00.000	10	2008-01-01 14:00:00.000
4	2008-01-01 19:25:00.000	5	2008-01-01 19:10:00.000
5	2008-01-01 19:10:00.000	4	2008-01-01 19:25:00.000
8	2008-01-01 14:45:00.000	9	2008-01-01 15:00:00.000
9	2008-01-01 15:00:00.000	8	2008-01-01 14:45:00.000
10	2008-01-01 14:00:00.000	3	2008-01-01 14:15:00.000
*/

Poor planning on your part does not constitute an emergency on my part.

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2008-03-08 : 17:06:32

DG,
Thanks for the reply...reading over it, I have one problem. I'm only able to use select statements. The interface that I use is limited so users can't do update, delete, create or insert queries.

I suppose this will not be possible simply using a Select query?

Thanks

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-08 : 17:18:44

The create and inserts were only to generate sample data for which to provide a result, and test with so that I provide a working answer.

If you are SQL 2000, you can't use the method without being able to create a temp table (or use too many resources)

It is possible using a select query...but if you have a lot of rows...not very effective due to http://www.sqlservercentral.com/articles/T-SQL/61539/
(something I just learned about courtesy of Jeff Moden)

Sample batch without removing the triangular join. Many apologies to Jeff Moden, in advance. If you have a LOT of rows, this can be nasty to do.



Create Table #bookings (BookingID int identity (1,1),Textcolumn char(1) not null)

Create Table #events (BookingID int not null, reqtime datetime not null)

Insert INTO #bookings (textcolumn)

Select  'A'
From master..spt_values
Where type = 'P' and number between 1 and 10

Insert into #events (bookingid,reqtime)
Select 1,'1/1/2008 1:00:00.000 PM' UNION ALL
Select 2,'1/1/2008 12:45:00.000 PM' UNION ALL
Select 3,'1/1/2008 2:15:00.000 PM' UNION ALL
Select 4,'1/1/2008 7:25:00.000 PM' UNION ALL
Select 5,'1/1/2008 7:10:00.000 PM' UNION ALL
Select 6,'1/1/2008 8:00:00.000 PM' UNION ALL
Select 7,'1/1/2008 8:30:00.000 PM' UNION ALL
Select 8,'1/1/2008 2:45:00.000 PM' UNION ALL
Select 9,'1/1/2008 3:00:00.000 PM' UNION ALL
Select 10,'1/1/2008 2:00:00.000 PM' 

--SQL 2000 not using creation of tables
Select DISTINCT e1.BookingID, e1.ReqTime,e2.BookingID as Conflict,e2.ReqTime as ConflictTime
FROM #events as e1 , #events as e2
Where ABS(datediff(mi,e1.ReqTime,e2.ReqTime)) <=15  AND e1.BookingID != e2.BookingID
order by BookingID

drop table #bookings
drop table #events
drop table #orderedevents


/*results
1	2008-01-01 13:00:00.000	2	2008-01-01 12:45:00.000
2	2008-01-01 12:45:00.000	1	2008-01-01 13:00:00.000
3	2008-01-01 14:15:00.000	10	2008-01-01 14:00:00.000
4	2008-01-01 19:25:00.000	5	2008-01-01 19:10:00.000
5	2008-01-01 19:10:00.000	4	2008-01-01 19:25:00.000
8	2008-01-01 14:45:00.000	9	2008-01-01 15:00:00.000
9	2008-01-01 15:00:00.000	8	2008-01-01 14:45:00.000
10	2008-01-01 14:00:00.000	3	2008-01-01 14:15:00.000
*/

In 2005, you can use the CTE example, otherwise..you should have the idea from what I have posted (3 working solutions based on your original post)

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources