January 31, 2013 at 1:13 pm
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
January 31, 2013 at 1:36 pm
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/
January 31, 2013 at 5:16 pm
I don't have a calendar table, its a vendor provided database with a holidays table only.
So I almost need to generate my calendar table on the fly. Or generate enough of a calendar table to definitively include the next business day.
CTE?
--This temp table is similar in structure to the vendor provided permanent table
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');
WITH Calendar as
(
SELECT getdate() AS CalendarDate UNION
SELECT getdate()+1 AS CalendarDate UNION
SELECT getdate()+2 AS CalendarDate UNION
SELECT getdate()+3 AS CalendarDate UNION
SELECT getdate()+4 AS CalendarDate UNION
SELECT getdate()+5 AS CalendarDate UNION
SELECT getdate()+6 AS CalendarDate UNION
SELECT getdate()+7 AS CalendarDate UNION
SELECT getdate()+8 AS CalendarDate UNION
SELECT getdate()+9 AS CalendarDate UNION
SELECT getdate()+10 AS CalendarDate
)
SELECT
MIN(CalendarDate)
FROM Calendar
WHERE
CalendarDate > getdate()
EXCEPT
SELECT
min(HolidayDate)
FROM #Holidays
WHERE
HolidayDate > getdate() AND
DATEPART(dd,HolidayDate) NOT IN (7,1)
DROP TABLE #Holidays
February 1, 2013 at 8:43 am
You don't want to use datepart(day...) here. That will return the day of the month. You want to use datepart(weekday...)
I like to use the full word instead of the abbreviation because it is much easier to read. So I use day instead dd etc.
If you have a tally table (you do right?), you can use it here.
SELECT min(NewDate)
FROM #Holidays
cross apply
(
select dateadd(day, N, HolidayDate) as NewDate
from Tally where N < 8
)x
where HolidayDate > getdate()
and DATEPART(weekday, NewDate) NOT IN (7,1)
_______________________________________________________________
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/
February 1, 2013 at 12:51 pm
This almost works based on a post by The Dixie Flatline here:
http://www.sqlservercentral.com/Forums/Topic649517-1472-1.aspx
His table has weekends in it, mine doesn't. I'm not sure where I can put the weekend restriction. My futile attempt to ungracefully add IF DATEPART(weekday, @future_date) = 7 fails because it is not 'holiday-aware'.
This can be demonstrated by setting the date to '2/15/2013'
set @start_date = '2/15/2013'
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');
declare @start_Date datetime
declare @future_Date datetime
declare @bizDays int
set @start_date = '2/1/2013'
set @bizdays = 1
select
top (@bizdays) @future_date = @start_Date + n
from tally t with(nolock)
left join #Holidays h with(nolock) on HolidayDate = @start_Date + n
where HolidayDate is null
DROP TABLE #Holidays
select @future_date
IF DATEPART(weekday, @future_date) = 7 SET @future_date = DATEADD(day,2,@future_date)
IF DATEPART(weekday, @future_date) = 1 SET @future_date = DATEADD(day,1,@future_date)
select @future_date
February 1, 2013 at 1:00 pm
That is quite similar to what I posted. I would recommend removing the nolock hints. They serve no purpose and run the possibility of making things bad...very bad.
_______________________________________________________________
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/
February 4, 2013 at 8:33 am
Thanks for setting me in the right direction...
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');
DECLARE @StartDate DATETIME
SET @StartDate = '2/15/2013'
SELECT
MIN((DATEADD(dd, DATEDIFF(dd, 0, @StartDate), 0) + n))
FROM TALLY T
LEFT JOIN #Holidays H
ON HolidayDate = (DATEADD(dd, DATEDIFF(dd, 0, @StartDate), 0) + n)
WHERE
HolidayDate IS NULL AND
DATEPART(WEEKDAY, (DATEADD(dd, DATEDIFF(dd, 0, @StartDate), 0) + n)) NOT IN (7,1)
DROP TABLE #Holidays
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply