Solved

Events that are not between dates

Posted on 2015-01-25
2
71 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 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

756 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