December 16, 2010 at 11:28 am
I need an efficient way to make sure that a date range does not overlap into a holiday. So, I have a holiday table:
DECLARE @Holidays TABLE(
HolidayName varchar(30)
,HolidayDate DateTime
)
INSERT INTO @Holidays(HolidayName, HolidayDate) VALUES('Christmas', '12/25/2011')
INSERT INTO @Holidays(HolidayName, HolidayDate) VALUES('Christmas Eve', '12/24/2011')
INSERT INTO @Holidays(HolidayName, HolidayDate) VALUES('New Years', '1/1/2011')
INSERT INTO @Holidays(HolidayName, HolidayDate) VALUES('Independance Day', '7/4/2011')
and I have some date range tables:
DECLARE @DateRanges TABLE(
StartDate DateTime,
EndDate DateTime
)
INSERT INTO @DateRanges(StartDate, EndDate) VALUES('1/5/2011 08:00 am', '1/5/2011 09:00 am')
INSERT INTO @DateRanges(StartDate, EndDate) VALUES('7/3/2011 11:00 pm', '7/3/2011 11:30 pm')
INSERT INTO @DateRanges(StartDate, EndDate) VALUES('7/3/2011 11:45 pm', '7/4/2011 01:00 am')
INSERT INTO @DateRanges(StartDate, EndDate) VALUES('8/1/2011 05:00 pm', '8/1/2011 06:00 pm')
INSERT INTO @DateRanges(StartDate, EndDate) VALUES('12/25/2011 11:45 pm', '12/26/2011 12:15 am')
INSERT INTO @DateRanges(StartDate, EndDate) VALUES('9/5/2011 08:00 am', '9/5/2011 09:00 am')
INSERT INTO @DateRanges(StartDate, EndDate) VALUES('3/10/2011 08:00 am', '3/12/2011 09:00 am')
INSERT INTO @DateRanges(StartDate, EndDate) VALUES('8/11/2011 12:01 am', '8/11/2011 06:00 am')
How do I select all the rows that don't overlap a holiday?
(I know this is the SQL 2005 forum, but I'm using SQL 2008 R2. I don't think we have a forum for that.)
Thanks!
.
December 16, 2010 at 11:59 am
I'm assuming that since your @DateRanges table has times that can start during the day, that you don't want any part of a holiday.
This should take care of it:
;
WITH CTE AS
(
-- strip the minutes from the dates in the @DateRanges table
SELECT StartDate = DATEADD(day, DateDiff(day, 0, StartDate), 0),
EndDate = DATEADD(day, DateDiff(day, 0, EndDate), 0)
FROM @DateRanges
)
SELECT CTE.*
FROM CTE
LEFT JOIN @Holidays h
ON h.HolidayDate BETWEEN CTE.StartDate and CTE.EndDate
WHERE h.HolidayDate IS NULL;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 16, 2010 at 12:51 pm
Looks good. I can make that work. Thanks Wayne!
.
December 16, 2010 at 1:40 pm
You're welcome. Thanks for the DDL/DML - it makes it a lot easier on us!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply