Solved

Events that are not between dates

Posted on 2015-01-25
2
61 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:
SimonAdept 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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article I will describe the Detach & Attach 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 video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

746 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now