I am wondering if anyone wants to try and better this code sample. Here is the request: Given a range of dates (start, end) return all rows that cross that range where the rows contain a start and end date.
This appears to work. I just wonder if anyone else has had a similar question.
Thanks,
CREATE TABLE #tblDateRange (
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nchar](10) NOT NULL,
[Start_Date] [datetime] NOT NULL,
[End_Date] [datetime] NOT NULL
) ON [PRIMARY]
insert into #tblDateRange (Name, start_date, end_date) values ('Andy ' ,'Jan 1 2012 12:00AM', 'Jan 21 2012 12:00AM')
insert into #tblDateRange (Name, start_date, end_date) values ('Tom ' ,'Jan 1 2012 12:00AM', 'Jan 31 2012 12:00AM')
insert into #tblDateRange (Name, start_date, end_date) values ('Frank ' ,'Jan 1 2012 12:00AM', 'Jan 11 2012 12:00AM')
insert into #tblDateRange (Name, start_date, end_date) values ('Joe ' ,'Jan 1 2012 12:00AM', 'Jan 3 2012 12:00AM')
insert into #tblDateRange (Name, start_date, end_date) values ('Hanna ' ,'Jan 1 2012 12:00AM', 'Jan 6 2012 12:00AM')
insert into #tblDateRange (Name, start_date, end_date) values ('Michelle ' ,'Aug 1 2012 12:00AM', 'Jan 8 2012 12:00AM')
insert into #tblDateRange (Name, start_date, end_date) values ('Meghan ' ,'Jul 1 2012 12:00AM', 'Jan 9 2012 12:00AM')
insert into #tblDateRange (Name, start_date, end_date) values ('Peter ' ,'Jun 1 2012 12:00AM', 'Jan 10 2012 12:00AM')
insert into #tblDateRange (Name, start_date, end_date) values ('Matthew ' ,'May 1 2012 12:00AM', 'Jan 12 2012 12:00AM')
insert into #tblDateRange (Name, start_date, end_date) values ('Ryan ' ,'Apr 1 2012 12:00AM', 'Jan 16 2012 12:00AM')
DECLARE @start_date AS DATETIME
DECLARE @end_date AS DATETIME
SET @start_date = '1/17/2012'
SET @end_date = '1/22/2012'
select * from #tblDateRange
select * from #tblDateRange
WHERE (( start_date between @start_date and @end_date and @end_date >= end_date)
OR ( start_date between @start_date and @end_date)
OR ( end_date between @start_date and @end_date )
OR ( end_date between @start_date and @end_date and @start_date<= start_date)
OR ( @start_date >= start_date AND @start_date <= end_date)
OR ( @end_date <= end_date AND @end_date >= start_date))