Solved

Events that are not between dates

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

803 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