Chrissy321 (1/31/2013)
Hi All,I am used to working with a calendar table that has a record for each day. Now I need to work with a table of holidays only.
My requirement is to find the next business day.
So logically, based on GETDATE() what is the next day that is not a Saturday or Sunday where that date does not exist in the #Holidays table.
I imagine I could do the loop + 1, check the variable and the do the loop as many time as needed but that would be sad.
Thanks if you can help.
CREATE TABLE #Holidays (HolidayDate datetime, IsHoliday char(1))
INSERT INTO #Holidays (HolidayDate,IsHoliday) VALUES('2013-02-18 00:00:00.000', 'Y')
INSERT INTO #Holidays (HolidayDate,IsHoliday) VALUES('2013-03-29 00:00:00.000', 'Y')
SELECT * FROM #Holidays
By using EXCEPT http://msdn.microsoft.com/en-us/library/ms188055.aspx
Something like this.
select min(CalendarTableDate)
from CalendarTable
where CalenderTableDate > getdate()
EXCEPT
select min(HolidayDate)
from #Holidays
where HolidayData > getdate()
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/