Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Next business day, partial holiday calendar Expand / Collapse
Author
Message
Posted Thursday, January 31, 2013 1:13 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 1:01 PM
Points: 643, Visits: 3,695
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
Posted Thursday, January 31, 2013 1:36 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:21 AM
Points: 11,935, Visits: 10,971
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1414320
Posted Thursday, January 31, 2013 5:16 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 1:01 PM
Points: 643, Visits: 3,695
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
Posted Friday, February 01, 2013 8:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:21 AM
Points: 11,935, Visits: 10,971
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1414710
Posted Friday, February 01, 2013 12:51 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 1:01 PM
Points: 643, Visits: 3,695
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
Posted Friday, February 01, 2013 1:00 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:21 AM
Points: 11,935, Visits: 10,971
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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1414825
Posted Monday, February 04, 2013 8:33 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 1:01 PM
Points: 643, Visits: 3,695
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 »

Add to briefcase

Permissions Expand / Collapse