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)
GO

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

 
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')

GO 

INSERT INTO Calendar  (CalendarDate) 
VALUES ('2014-08-01'),
       ('2014-08-02'),
       ('2014-08-03'),
       ('2014-08-04'),
       ('2014-08-05'),
       ('2014-08-06'),
       ('2014-08-07'),
       ('2014-08-08'),
       ('2014-08-09'),
       ('2014-08-10'),
       ('2014-08-11'),
       ('2014-08-12'),
       ('2014-08-13'),
       ('2014-08-14'),
       ('2014-08-15'),
       ('2014-08-16'),
       ('2014-08-17'),
       ('2014-08-18'),
       ('2014-08-19'),
       ('2014-08-20') 


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 



 
Advertisements


Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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