Next business day, partial holiday calendar

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Next business day, partial holiday calendar

Next business day, partial holiday calendar

Rate Topic

Display Mode

Topic Options

Author

Message

Chrissy321

Posted Thursday, January 31, 2013 1:13 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 3:25 PM
Points: 553, Visits: 3,000

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

Post #1414307

Sean Lange

Posted Thursday, January 31, 2013 1:36 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 2:33 PM
Points: 8,620, Visits: 8,261

Chrissy321 (1/31/2013)

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1414320

Chrissy321

Posted Thursday, January 31, 2013 5:16 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 3:25 PM
Points: 553, Visits: 3,000

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

Post #1414405

Sean Lange

Posted Friday, February 01, 2013 8:43 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 2:33 PM
Points: 8,620, Visits: 8,261

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)

Post #1414710

Chrissy321

Posted Friday, February 01, 2013 12:51 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 3:25 PM
Points: 553, Visits: 3,000

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

Post #1414820

Sean Lange

Posted Friday, February 01, 2013 1:00 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 2:33 PM
Points: 8,620, Visits: 8,261

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1414825

Chrissy321

Posted Monday, February 04, 2013 8:33 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 3:25 PM
Points: 553, Visits: 3,000

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

Post #1415345

« Prev Topic | Next Topic »

Permissions