Solved

Events that are not between dates

Posted on 2015-01-25
2
77 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 18

Accepted Solution

by:
Simon earned 500 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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

615 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