Search Date Range with a Calendar Table — a Sample with EXISTs or JOIN

 set statistics IO on
set statistics time on

create table DateRange( id int identity(1,1),
	StartDate DATE,	FinishDate DATE,	Condition BIT)

create table Calendar(id int identity(1,1),CalendarDate DATE)

INSERT INTO DateRange (StartDate,FinishDate,Condition) 
VALUES ('2014-08-02','2014-08-03','true'), ('2014-08-03', '2014-08-13', 'false'),  ('2014-08-13', '2014-08-14', 'true')


INSERT INTO Calendar  (CalendarDate) 
VALUES ('2014-08-01'),

select CalendarDate
from Calendar c
where EXISTS (
	select C.CalendarDate 
	FROM  DateRange dr
	where c.CalendarDate between dr.StartDate and dr.FinishDate and dr.Condition = 'true'
select CalendarDate 
FROM DateRange dr
JOIN Calendar c ON c.CalendarDate >= dr.StartDate AND c.CalendarDate <=dr.FinishDate AND dr.Condition = 'true';

drop table DateRange, Calendar

set statistics IO off
set statistics time off 


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s