No events for this site between a date/time range

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 2005 Forums

Transact-SQL (2005)

No events for this site between a date/time range

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-09-15 : 00:48:54

The user enters a time/date range as StartDateRange and EndDateRange. I would like to return only the Siteid's where there are no Eventid's between this range as per the EventStartTime. The SiteDetails.SiteID could have no matching eventid's at all or matching eventid's before or after the user time/date range. Basically trying to report if no events have occured during the user selected time/date range or at all ever for a site.

Table 1 (SiteDetails)

SiteID (key)
SiteName

Table 2 (EventDetails)

EventID (key)
EventStartTime
EventEndTime
SiteID

The following returns if the siteid has never had a matching event but does not consider if the site has events outside the time/date range:
WHERE SiteDetails.SiteID NOT IN (SELECT SiteID FROM EventDetails)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-15 : 00:57:42

Then add a date range to the subquery. If that doesn't help, please provide sample data that illustrates your issue as it can be hard to dicipher what people are saying with just words. Sample data helps us understand your issue.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-15 : 01:07:57

may be this:-

SELECT sd.*
FROM SiteDetails sd
LEFT JOIN (SELECT * FROM EventDetails WHERE EventStartTime>=@UserStartTime AND EventEndTime<=@UserEndTime)ed
ON ed.SiteID=sd.SiteID
WHERE ed.SiteID IS NULL

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-09-15 : 01:24:46

I have being trying to add the time/date filter but this is where I seem to be going wrong. Thanks

User selected range: 1/1/2008 12:00:00am to 3/1/2008 12:00:00am

Table 1 (SiteDetails)

SiteID (key) SiteName
1 Ben
2 Steve
3 John
4 Adam
5 No Events

Table 2 (EventDetails)

EventID (key) SiteID EventStartTime EventEndTime
1 1 1/1/2008 12:00:00am 1/1/2008 01:00:00am
2 2 1/1/2008 02:00:00pm 10/1/2008 05:00:00pm
3 2 5/1/2008 12:00:00am 6/1/2008 01:00:00am
4 3 5/1/2008 12:00:00am 6/1/2008 01:00:00am
5 4 31/12/2007 12:00:00am 31/12/2007 11:59:59pm

Expected Result:
SiteID (key) SiteName
3 John
4 Adam
5 No Events

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-15 : 01:49:07

Not sure how you would get 3 John among output its startdate is 5/1/2008 which is outside your input range.can you explain this?

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-09-15 : 01:58:44

Note dates are in DD/MM/YYYY format here. I also dont care about eventendtime. Thats the key to this. I need all the sites which have no events within the user selected time range or no events at all.

Expected Result:
SiteID (key) SiteName
3 John < This will be returned because the eventstarttime is beyond the user range.
4 Adam < This will be returned because the eventstarttime is before the user range.
5 No Events < This will be returned because the SiteDetails.SiteID has no matching EventDetails.SiteID at all in the DB.

SiteID (key) SiteName
1 Ben < This will not be returned because the eventstarttime is between the user range.
2 Steve < This will not be returned because it has one event where the eventstarttime is between the user range.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-15 : 02:21:18

try this out:-

declare @SiteDetails table
(
SiteID int,
SiteName varchar(20))
insert into @SiteDetails
select 1, 'Ben' union all
select 2, 'Steve' union all
select 3, 'John' union all
select 4, 'Adam' union all
select 5, 'No Events'

declare @EventDetails table
(
EventID int,
 SiteID int,
EventStartTime datetime,
EventEndTime  datetime
)
insert into @EventDetails
select 1, 1, '1/1/2008 12:00:00am', '1/1/2008 01:00:00am' union all
select 2, 2, '1/1/2008 02:00:00pm', '10/1/2008 05:00:00pm' union all
select 3, 2, '5/1/2008 12:00:00am', '6/1/2008 01:00:00am' union all
select 4, 3, '5/1/2008 12:00:00am', '6/1/2008 01:00:00am' union all
select 5 ,4, '31/12/2007 12:00:00am', '31/12/2007 11:59:59pm'


SELECT sd.*
FROM @SiteDetails sd
LEFT JOIN (SELECT SiteID,
SUM(CASE WHEN (EventStartTime>='1/1/2008 12:00:00am' 
 AND EventStartTime<= '3/1/2008 12:00:00am')
OR  (EventEndTime>='1/1/2008 12:00:00am' 
 AND EventEndTime<= '3/1/2008 12:00:00am') THEN 1 ELSE 0 END) AS Overlap
 FROM @EventDetails 
GROUP BY SiteID
)ed
ON ed.SiteID=sd.SiteID
AND ed.Overlap=1
WHERE ed.SiteID IS NULL

output
------------------------
SiteID      SiteName
----------- --------------------
3           John
4           Adam
5           No Events

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-09-15 : 02:56:21

Thanks I will test it.

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-09-15 : 03:05:34

Seems close but not quite there:

declare @SiteDetails table
(
SiteID int,
SiteName varchar(20))
insert into @SiteDetails
select 1, 'Ben' union all
select 2, 'Steve' union all
select 3, 'John' union all
select 4, 'Adam' union all
select 5, 'No Events'

declare @EventDetails table
(
EventID int,
SiteID int,
EventStartTime datetime,
EventEndTime datetime
)
insert into @EventDetails
select 1, 1, '1/1/2008 12:00:00am', '1/1/2008 01:00:00am' union all
select 2, 2, '1/1/2008 02:00:00pm', '1/10/2008 05:00:00pm' union all
select 3, 2, '1/5/2008 12:00:00am', '1/6/2008 01:00:00am' union all
select 4, 3, '1/5/2008 12:00:00am', '1/6/2008 01:00:00am' union all
select 5 ,4, '12/31/2007 12:00:00am', '12/31/2007 11:59:59pm'

SELECT sd.*
FROM @SiteDetails sd
LEFT JOIN (SELECT SiteID,
SUM(CASE WHEN (EventStartTime>='1/1/2000 12:00:00am'
AND EventStartTime<= '1/1/2010 12:00:00am')
OR (EventEndTime>='1/1/2000 12:00:00am'
AND EventEndTime<= '1/1/2010 12:00:00am') THEN 1 ELSE 0 END) AS Overlap
FROM @EventDetails
GROUP BY SiteID
)ed
ON ed.SiteID=sd.SiteID
AND ed.Overlap=1
WHERE ed.SiteID IS NULL

The dates above return:

SiteID 2 and 5 when only 5 should be returned as 5 has no events but siteid 2 now has 2 events within the user date range.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-15 : 03:18:57

quote:
Originally posted by harlingtonthewizard

Seems close but not quite there:

declare @SiteDetails table
(
SiteID int,
SiteName varchar(20))
insert into @SiteDetails
select 1, 'Ben' union all
select 2, 'Steve' union all
select 3, 'John' union all
select 4, 'Adam' union all
select 5, 'No Events'

declare @EventDetails table
(
EventID int,
SiteID int,
EventStartTime datetime,
EventEndTime datetime
)
insert into @EventDetails
select 1, 1, '1/1/2008 12:00:00am', '1/1/2008 01:00:00am' union all
select 2, 2, '1/1/2008 02:00:00pm', '1/10/2008 05:00:00pm' union all
select 3, 2, '1/5/2008 12:00:00am', '1/6/2008 01:00:00am' union all
select 4, 3, '1/5/2008 12:00:00am', '1/6/2008 01:00:00am' union all
select 5 ,4, '12/31/2007 12:00:00am', '12/31/2007 11:59:59pm'

SELECT sd.*
FROM @SiteDetails sd
LEFT JOIN (SELECT SiteID,
SUM(CASE WHEN (EventStartTime>='1/1/2000 12:00:00am'
AND EventStartTime<= '1/1/2010 12:00:00am')
OR (EventEndTime>='1/1/2000 12:00:00am'
AND EventEndTime<= '1/1/2010 12:00:00am') THEN 1 ELSE 0 END) AS Overlap
FROM @EventDetails
GROUP BY SiteID
)ed
ON ed.SiteID=sd.SiteID
AND ed.Overlap>0WHERE ed.SiteID IS NULL

The dates above return:

SiteID 2 and 5 when only 5 should be returned as 5 has no events but siteid 2 now has 2 events within the user date range.

change like above and try

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-09-15 : 04:52:07

Thankyou very much for your time, this works great!! Now I just need to study it to work out how it works:)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-15 : 04:59:16

quote:
Originally posted by harlingtonthewizard

Thankyou very much for your time, this works great!! Now I just need to study it to work out how it works:)

You're welcome

Let us know if you still have any doubt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources