November 21, 2014 at 10:58 am
Hi All,
I have a stored procedure that has to deliver the next 11 years of business days, accounting for weekends and holidays. The holiday table is, of necessity, built to allow date calculation of the observed holiday date in any given year. Here's what I have so far, and it takes a good 32 seconds to run. I'm hopeful I can find some help in getting that execution time down without eliminating the date calculation on holidays, as that HAS to continue to exist and I don't get to change that.
CREATE FUNCTION dbo.fn_BusinessDaysStartingToday (
@TODAY date
)
RETURNS @BUSINESS_DAYS TABLE (RN int PRIMARY KEY CLUSTERED, THE_DATE date, WEEK_DAY varchar(10), BUSINESS_DAY char(1), HOLIDAY_DAY char(1))
AS
BEGIN
SET @TODAY = ISNULL(@TODAY, GETDATE())
;WITH YEARS AS (
SELECT YEAR(@TODAY) AS YR
UNION ALL
SELECT YR + 1
FROM YEARS
WHERE YR + 1 < YEAR(@TODAY) + 12
),
CALENDAR_DATES AS (
SELECT C.YR, C.THE_DATE
FROM YEARS AS Y
CROSS APPLY dbo.fn_CalendarDaysByYear(Y.YR) AS C
WHERE C.THE_DATE >= @TODAY
),
HOLIDAYS AS (
SELECT Y.YR, HolidayDate
FROM YEARS AS Y
CROSS APPLY dbo.fn_HolidaysByYear(Y.YR)
),
CALENDAR_DAYS AS (
SELECT DISTINCT T.THE_DATE,
CASE
WHEN DATEPART(dw, T.THE_DATE) IN (1, 7) THEN 'N'
WHEN T.THE_DATE = H.HolidayDate THEN 'N'
ELSE 'Y'
END AS BUSINESS_DAY,
CASE
WHEN H.HolidayDate IS NULL THEN 'N'
ELSE 'Y'
END AS HOLIDAY_DAY,
DATENAME(dw, T.THE_DATE) AS WEEK_DAY
FROM CALENDAR_DATES AS T
LEFT OUTER JOIN HOLIDAYS AS H
ON T.THE_DATE = H.HolidayDate
)
INSERT INTO @BUSINESS_DAYS (RN, THE_DATE, WEEK_DAY, BUSINESS_DAY, HOLIDAY_DAY)
SELECT ROW_NUMBER() OVER(ORDER BY C.THE_DATE) - 1 AS RN,
C.THE_DATE, C.WEEK_DAY, C.BUSINESS_DAY, C.HOLIDAY_DAY
FROM CALENDAR_DAYS AS C
WHERE BUSINESS_DAY = 'Y'
OPTION (MAXRECURSION 6000)
RETURN
END
GO
Here's the holiday function:
/****** Object: UserDefinedFunction [dbo].[fn_HolidaysByYear] Script Date: 11/21/2014 12:06:19 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_HolidaysByYear]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_HolidaysByYear]
GO
CREATE FUNCTION dbo.fn_HolidaysByYear (
@YR int
)
RETURNS @HolidayTable TABLE (HolidayDate date NOT NULL PRIMARY KEY NONCLUSTERED, YR int, HolidayID int NOT NULL)
AS
BEGIN
--DECLARE @YR AS int = 2014, @HolidayTable AS TABLE (HolidayID int PRIMARY KEY CLUSTERED, HolidayDate date NOT NULL);
;WITH ORIGINAL_HOLIDAYS AS (
SELECT H.HolidayID, H.HolidayName, H.WkendMove, H.OtherHolidayID,
CAST(
CASE H.HolidayType
WHEN 'FIXED' THEN CAST(CAST(@YR AS char(4)) + '-' + RIGHT('0' + CAST(H.HolidayMonth AS varchar(2)), 2) + '-' + RIGHT('0' + CAST(H.HolidayDay AS varchar(2)), 2) AS date)
WHEN 'EASTERBASE' THEN DATEADD(dd, H.DaysDifference, CAST(dbo.fn_EasterSundayByYear(@YR) AS datetime))
WHEN 'LASTONE'
THEN DATEADD(dd,
CASE DATEPART(dw, DATEADD(dd, -1, DATEADD(mm, 1, CAST(CAST(@YR AS char(4)) + '-' + RIGHT('0' + CAST(H.HolidayMonth AS varchar(2)), 2) + '-01' AS datetime))))
WHEN 1 THEN -6
ELSE 2 - DATEPART(dw, DATEADD(dd, -1, DATEADD(mm, 1, CAST(CAST(@YR AS char(4)) + '-' + RIGHT('0' + CAST(H.HolidayMonth AS varchar(2)), 2) + '-01' AS datetime))))
END,
DATEADD(dd, -1, DATEADD(mm, 1, CAST(CAST(@YR AS char(4)) + '-' + RIGHT('0' + CAST(H.HolidayMonth AS varchar(2)), 2) + '-01' AS datetime))))
WHEN 'NUMBERED' THEN CAST(CAST(@YR AS varchar(4)) + '-' + RIGHT('0' + CAST(H.HolidayMonth AS varchar(2)), 2) + '-' +
RIGHT('0' + CAST(HC.StartDay + ((H.NumberedDay - 1) * 7) AS varchar(2)), 2) AS date)
WHEN 'HOLIDAYBASE' THEN DATEADD(dd, H.DaysDifference, CAST(
CASE HO.HolidayType
WHEN 'FIXED' THEN CAST(CAST(@YR AS char(4)) + '-' + RIGHT('0' + CAST(HO.HolidayMonth AS varchar(2)), 2) + '-' + RIGHT('0' + CAST(HO.HolidayDay AS varchar(2)), 2) AS date)
WHEN 'EASTERBASE' THEN DATEADD(dd, HO.DaysDifference, CAST(dbo.fn_EasterSundayByYear(@YR) AS datetime))
WHEN 'LASTONE'
THEN DATEADD(dd,
CASE DATEPART(dw, DATEADD(dd, -1, DATEADD(mm, 1, CAST(CAST(@YR AS char(4)) + '-' + RIGHT('0' + CAST(HO.HolidayMonth AS varchar(2)), 2) + '-01' AS datetime))))
WHEN 1 THEN -6
ELSE 2 - DATEPART(dw, DATEADD(dd, -1, DATEADD(mm, 1, CAST(CAST(@YR AS char(4)) + '-' + RIGHT('0' + CAST(HO.HolidayMonth AS varchar(2)), 2) + '-01' AS datetime))))
END,
DATEADD(dd, -1, DATEADD(mm, 1, CAST(CAST(@YR AS char(4)) + '-' + RIGHT('0' + CAST(HO.HolidayMonth AS varchar(2)), 2) + '-01' AS datetime))))
WHEN 'NUMBERED' THEN CAST(CAST(@YR AS varchar(4)) + '-' + RIGHT('0' + CAST(HO.HolidayMonth AS varchar(2)), 2) + '-' +
RIGHT('0' + CAST(HOC.StartDay + ((HO.NumberedDay - 1) * 7) AS varchar(2)), 2) AS date)
END AS datetime))
END AS date) AS HolidayDate
FROM dbo.BCN_Holidays AS H
LEFT OUTER JOIN dbo.BCN_Holidays_Compute AS HC
ON H.DayOfTheWeek = HC.WeekDayDesired
AND DATEPART(dw, CAST(CAST(@YR AS char(4)) + '-' + RIGHT('0' + CAST(H.HolidayMonth AS varchar(2)), 2) + '-01' AS datetime)) = HC.WeekDayFirstOfTheMonth
LEFT OUTER JOIN dbo.BCN_Holidays AS HO
ON H.OtherHolidayID = HO.HolidayID
LEFT OUTER JOIN dbo.BCN_Holidays_Compute AS HOC
ON HO.DayOfTheWeek = HOC.WeekDayDesired
AND DATEPART(dw, CAST(CAST(@YR AS char(4)) + '-' + RIGHT('0' + CAST(HO.HolidayMonth AS varchar(2)), 2) + '-01' AS datetime)) = HOC.WeekDayFirstOfTheMonth
),
WKEND_ADJUSTED_HOLIDAYS AS (
SELECT HolidayID, OtherHolidayID, WkendMove,
CASE WkendMove
WHEN 0 THEN HolidayDate
ELSE
CASE DATEPART(dw, HolidayDate)
WHEN 1 THEN DATEADD(dd, 1, HolidayDate)
WHEN 7 THEN DATEADD(dd, -1, HolidayDate)
ELSE HolidayDate
END
END AS HolidayDate
FROM ORIGINAL_HOLIDAYS AS X
)
INSERT INTO @HolidayTable
SELECT
CASE WkendMove
WHEN 0 THEN HolidayDate
ELSE
CASE
WHEN EXISTS (
SELECT 1
FROM WKEND_ADJUSTED_HOLIDAYS AS Z1
INNER JOIN dbo.BCN_Holidays AS H1
ON Z1.HolidayID = H1.OtherHolidayID
WHERE Z1.HolidayID = Y.HolidayID
AND MONTH(Y.HolidayDate) = H1.HolidayMonth
AND DAY(Y.HolidayDate) = H1.HolidayDay
) THEN DATEADD(dd, CASE DATEPART(dw, Y.HolidayDate) WHEN 2 THEN -3 WHEN 6 THEN 3 END, Y.HolidayDate)
ELSE Y.HolidayDate
END
END AS HolidayDate,
@YR AS YR,
HolidayID
FROM WKEND_ADJUSTED_HOLIDAYS AS Y
--ORDER BY 1
RETURN
END
GO
Here's the holiday tables:
CREATE TABLE dbo.BCN_Holidays(
HolidayID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
HolidayName varchar(30) NOT NULL,
HolidayType varchar(15) NOT NULL,
HolidayMonth tinyint,
HolidayDay tinyint,
NumberedDay tinyint,
DayOfTheWeek tinyint,
DaysDifference int,
OtherHolidayID int,
WkendMove bit NOT NULL
)
/*-- INSERT THE FOLLOWING CSV DATA
HolidayID,HolidayName,HolidayType,HolidayMonth,HolidayDay,NumberedDay,DayOfTheWeek,DaysDifference,OtherHolidayID,WkendMove
1,New Year's Day,FIXED,1,1,NULL,NULL,NULL,10,1
2,Good Friday,EASTERBASE,NULL,NULL,NULL,NULL,-2,NULL,0
3,Memorial Day,LASTONE,5,NULL,NULL,2,NULL,NULL,0
4,Independence Day,FIXED,7,4,NULL,NULL,NULL,NULL,1
5,Labor Day,NUMBERED,9,NULL,1,2,NULL,NULL,0
6,Thanksgiving Day,NUMBERED,11,NULL,4,5,NULL,NULL,0
7,Black Friday,HOLIDAYBASE,NULL,NULL,NULL,NULL,1,6,0
8,Christmas Eve,FIXED,12,24,NULL,NULL,-1,9,1
9,Christmas Day,FIXED,12,25,NULL,NULL,NULL,8,1
10,New Year's Eve,FIXED,12,31,NULL,NULL,NULL,1,1
*/
/****** Object: Table [dbo].[BCN_Holidays_Compute] Script Date: 11/21/2014 12:52:50 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BCN_Holidays_Compute]') AND type in (N'U'))
DROP TABLE [dbo].[BCN_Holidays_Compute]
GO
CREATE TABLE dbo.BCN_Holidays_Compute(
WeekDayDesired tinyint NOT NULL,
WeekDayFirstOfTheMonth tinyint NOT NULL,
StartDay tinyint NOT NULL
)
GO
/*--INSERT THE FOLLOWING CSV DATA
WeekDayDesired,WeekDayFirstOfTheMonth,StartDay
1,1,1
1,2,7
1,3,6
1,4,5
1,5,4
1,6,3
1,7,2
2,1,2
2,2,1
2,3,7
2,4,6
2,5,5
2,6,4
2,7,3
3,1,3
3,2,2
3,3,1
3,4,7
3,5,6
3,6,5
3,7,4
4,1,4
4,2,3
4,3,2
4,4,1
4,5,7
4,6,6
4,7,5
5,1,5
5,2,4
5,3,3
5,4,2
5,5,1
5,6,7
5,7,6
6,1,6
6,2,5
6,3,4
6,4,3
6,5,2
6,6,1
6,7,7
7,1,7
7,2,6
7,3,5
7,4,4
7,5,3
7,6,2
7,7,1
*/
I may just need a better method to get this to perform better, but I am constrained by the holiday computation. Any and all help appreciated.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 21, 2014 at 2:25 pm
Hi sgmunson,
Thank you for all the code and data you provided, but there's a lot going on there, and as is, those functions can't be run (since they call other functions for which the code is not provided). I'm also not clear on which portion of your code is taking 32 seconds and what needs to be optimized. Can you provide the missing functions so that your code can be executed?
BTW, I scripted out the insert statements for the CSV data you provided in case anyone else wants to give this a stab and compile the data:
--bcn_holidays
insert into bcn_holidays
select 1, 'New Year''s Day','FIXED',1,1,NULL,NULL,NULL,10,1
union all select 2, 'Good Friday','EASTERBASE',NULL,NULL,NULL,NULL,-2,NULL,0
union all select 3, 'Memorial Day','LASTONE',5,NULL,NULL,2,NULL,NULL,0
union all select 4, 'Independence Day','FIXED',7,4,NULL,NULL,NULL,NULL,1
union all select 5, 'Labor Day','NUMBERED',9,NULL,1,2,NULL,NULL,0
union all select 6, 'Thanksgiving Day','NUMBERED',11,NULL,4,5,NULL,NULL,0
union all select 7, 'Black Friday','HOLIDAYBASE',NULL,NULL,NULL,NULL,1,6,0
union all select 8, 'Christmas Eve','FIXED',12,24,NULL,NULL,-1,9,1
union all select 9, 'Christmas Day','FIXED',12,25,NULL,NULL,NULL,8,1
union all select 10, 'New Year''s Eve','FIXED',12,31,NULL,NULL,NULL,1,1
--bcn_holdays_compute
insert into bcn_holidays_compute
select 1,1,1
union all select 1,2,7
union all select 1,3,6
union all select 1,4,5
union all select 1,5,4
union all select 1,6,3
union all select 1,7,2
union all select 2,1,2
union all select 2,2,1
union all select 2,3,7
union all select 2,4,6
union all select 2,5,5
union all select 2,6,4
union all select 2,7,3
union all select 3,1,3
union all select 3,2,2
union all select 3,3,1
union all select 3,4,7
union all select 3,5,6
union all select 3,6,5
union all select 3,7,4
union all select 4,1,4
union all select 4,2,3
union all select 4,3,2
union all select 4,4,1
union all select 4,5,7
union all select 4,6,6
union all select 4,7,5
union all select 5,1,5
union all select 5,2,4
union all select 5,3,3
union all select 5,4,2
union all select 5,5,1
union all select 5,6,7
union all select 5,7,6
union all select 6,1,6
union all select 6,2,5
union all select 6,3,4
union all select 6,4,3
union all select 6,5,2
union all select 6,6,1
union all select 6,7,7
union all select 7,1,7
union all select 7,2,6
union all select 7,3,5
union all select 7,4,4
union all select 7,5,3
union all select 7,6,2
union all select 7,7,1
November 21, 2014 at 2:42 pm
As I'm thinking about this more (with the somewhat limited amount of information I have) two things come to mind.
The first is to build a calendar table; basically an enhanced tally table with the breakdowns of dates, years, months, weekends, etc. This gets you over the hurdle of a lot of the date masking and weekend problems. I've provided a sample script of this below.
But the second thing is, why do you have to do this all on the fly each time? Since the holidays and weekends aren't going to change (presumably unless you update the contents of your bcn_holidays and bcn_holidays_compute tables), you can just run this once and persist off as many years into the future you like. Even if the initial population takes half an hour to go 5000 years into the future or something, after the initial run, all your queries could then just reference a new, precalculated calendar table with holidays already excluded.
Calendar Table Code
declare @eMsg varchar(2000)
if object_id('tempdb.dbo.#cal') is not null drop table #cal
create table #cal
(
cDateInt int primary key clustered,
cDate as cast(cast(cDateInt as datetime) -2 as date) persisted,
cDateDt as cast(cDateInt as datetime) -2 persisted,
cMonthChar as cast(case when datepart(month, cast(cDateInt as datetime) -2) < 10 then '0'
else ''
end + + cast(datepart(month, cast(cDateInt as datetime) - 2) as varchar(30)) as char(2)) persisted,
cMonthInt as cast(datepart(month, cast(cDateInt as datetime) - 2) as tinyint) persisted,
cQtrChar as cast(datepart(qq, cast(cDateInt as datetime) - 2) as char(1)) persisted,
cQtrInt as cast(datepart(qq, cast(cDateInt as datetime) - 2) as tinyint) persisted,
cYearChar as cast(datepart(yyyy, cast(cDateInt as datetime) - 2) as char(4)) persisted,
cYearInt as cast(datepart(yyyy, cast(cDateInt as datetime) - 2) as smallint) persisted,
cWeekdayNum as datepart(weekday, cast(cDateInt as datetime) - 2),
cWeekdayName as datename(weekday, cast(cDateInt as datetime) -2) ,
cMonthName as datename(month, cast(cDateInt as datetime) - 2),
isWeekend as case datepart(weekday, cast(cDateInt as datetime) - 2) when 1 then 1
when 7 then 1
else 0
end
)
insert into #cal (cDateInt)
select top 100000 row_number() over (order by (select null))- 1
from sys.objects a, sys.objects b
November 21, 2014 at 2:43 pm
I use a different approach.
I have a nonbusiness dates control table where one first defines the general and specific nonbusiness dates wanted. Then you call a stored proc with a year range to gen the actual nonbusiness dates table. It gens 16 years in a sec or two. The tables have a FK relationship. While you could change the final dates table by hand, the idea is that you change the controls/definition and the gen the actual data, simply to cut down on errors.
If you'd genuinely interested in possibly using this approach, let me know and I can post the code.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 28, 2014 at 2:13 am
JeeTee (11/21/2014)
As I'm thinking about this more (with the somewhat limited amount of information I have) two things come to mind.The first is to build a calendar table; basically an enhanced tally table with the breakdowns of dates, years, months, weekends, etc. This gets you over the hurdle of a lot of the date masking and weekend problems. I've provided a sample script of this below.
But the second thing is, why do you have to do this all on the fly each time? Since the holidays and weekends aren't going to change (presumably unless you update the contents of your bcn_holidays and bcn_holidays_compute tables), you can just run this once and persist off as many years into the future you like. Even if the initial population takes half an hour to go 5000 years into the future or something, after the initial run, all your queries could then just reference a new, precalculated calendar table with holidays already excluded.
Calendar Table Code
declare @eMsg varchar(2000)
if object_id('tempdb.dbo.#cal') is not null drop table #cal
create table #cal
(
cDateInt int primary key clustered,
cDate as cast(cast(cDateInt as datetime) -2 as date) persisted,
cDateDt as cast(cDateInt as datetime) -2 persisted,
cMonthChar as cast(case when datepart(month, cast(cDateInt as datetime) -2) < 10 then '0'
else ''
end + + cast(datepart(month, cast(cDateInt as datetime) - 2) as varchar(30)) as char(2)) persisted,
cMonthInt as cast(datepart(month, cast(cDateInt as datetime) - 2) as tinyint) persisted,
cQtrChar as cast(datepart(qq, cast(cDateInt as datetime) - 2) as char(1)) persisted,
cQtrInt as cast(datepart(qq, cast(cDateInt as datetime) - 2) as tinyint) persisted,
cYearChar as cast(datepart(yyyy, cast(cDateInt as datetime) - 2) as char(4)) persisted,
cYearInt as cast(datepart(yyyy, cast(cDateInt as datetime) - 2) as smallint) persisted,
cWeekdayNum as datepart(weekday, cast(cDateInt as datetime) - 2),
cWeekdayName as datename(weekday, cast(cDateInt as datetime) -2) ,
cMonthName as datename(month, cast(cDateInt as datetime) - 2),
isWeekend as case datepart(weekday, cast(cDateInt as datetime) - 2) when 1 then 1
when 7 then 1
else 0
end
)
insert into #cal (cDateInt)
select top 100000 row_number() over (order by (select null))- 1
from sys.objects a, sys.objects b
+1
Works perfectly for our scheduling task scenario. Thanks a ton.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply