Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Events that are not between dates

Posted on 2015-01-25
2
Medium Priority
?
82 Views
Last Modified: 2015-01-25
Hello,
I have a table with events and each event has a date.
I want to select all events that do not collide with periods of another table.
For example:
Events table:  EventId int, MyDate date,
Execption table:  PeriodId int, StartDate date, EndDate date

EventTable
EventId=1, MyDate='2015-01-03'
EventId=2, MyDate='2015-01-25'

ExceptionTable
PeriodId=1, StartDate='2015-01-02', EndDate='2015-01-08'

The result would be EventId=2, MyDate='2015-01-25'
The reason why eventId=1 is not selected is because it is between startdate and enddate of the exception table.

What is the most efficent way to write a query for this?
0
Comment
Question by:johnson1
2 Comments
 
LVL 18

Accepted Solution

by:
Simon earned 2000 total points
ID: 40569197
This works:
SELECT * 
FROM Eventtable E  
WHERE NOT EXISTS
  (SELECT * 
  FROM Exceptiontable ET 
  WHERE E.MyDate >=ET.startdate AND E.MyDate <=ET.enddate)

Open in new window


This is a worked example (using table variables for ease of testing)
declare @EventTable table(eventid int,MyDate date)
insert into @eventtable
select 1,'20150103' union 
select 2,'20150125'

declare @ExceptionTable table (PeriodID int,startdate date,enddate date)
insert into @exceptionTable
select 1,'20150102','20150108'

select * from @eventtable E  where not exists (select * from @exceptiontable ET where E.MyDate >=ET.startdate and e.MyDate <=ET.enddate)

Open in new window


The other approach is to build a date table or CTE to store each date in the exception ranges, but for most applications I'd just use the above method.
0
 

Author Closing Comment

by:johnson1
ID: 40569650
Thank you:-)
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

927 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question