November 8, 2015 at 8:14 am
Hello all,
I have a business need to come up with a function that calculates the last working day (monday-friday). The reason I need this is for comparison between this year and last years data. The data needs to be calculated based on the same number of working days.
Using some of the logic that I found in "http://stackoverflow.com/questions/252519/count-work-days-between-two-dates", I know how many working days we've had this year.
Now I need to to use that number to calculate the last working day in the same period last year.
I came up with the function below. It works fine as it relates to weekends, starting points and ending points. Where it becomes tricky is that when there is holiday in my holiday table, i want to include that in the logic as well. I can't simply move the end date one day further in case it ends on a holiday. What happens if there are two holidays in a row? Not sure how to incorporate that.
Any help is appreciated.
USE [master]
GO
/****** Object: UserDefinedFunction [dbo].[fn_WorkDaysEndDate] Script Date: 11/8/2015 8:05:23 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fn_WorkDaysEndDate]
--SELECT [Master].[dbo].[fn_WorkDaysEndDate] ( '11/7/2015', 11)
(
@EnteredDate DATETIME
,@WorkingDays INT
)
--Define the output data type.
RETURNS DATETIME
AS
--Calculate the RETURN of the function.
BEGIN
DECLARE @StartDate DATETIME = @EnteredDate
-- in case @EnteredDate starts on Saturday or Sunday, let @WorkingStartDate start on Monday
DECLARE @WorkingStartDate DATETIME = (
SELECT DateAdd(day, CASE
WHEN DATENAME(WEEKDAY, @EnteredDate) = 'Saturday'
THEN 2
WHEN DATENAME(WEEKDAY, @EnteredDate) = 'Sunday'
THEN 1
ELSE 0
END, @EnteredDate)
)
--End Date = (@WorkingStartDate -1) <<to count the startdate itself>> + Working Days
DECLARE @EndDate DATETIME = (
SELECT Dateadd(DAY, @WorkingDays - 1, @WorkingStartDate)
)
--Calculate all dates between Start and End Date and store them in @temp
SELECT @StartDate = @StartDate
DECLARE @temp TABLE (DATE DATETIME)
WHILE (@StartDate <= @EndDate)
BEGIN
INSERT INTO @temp
VALUES (@StartDate)
SELECT @StartDate = DATEADD(DD, 1, @StartDate)
END
--SELECT Datename(Month,d.Date) as MonthName, Datename(WEEK,D.date) as WeekNumber, D.Date,Day(D.Date) as Day,
--Month(D.Date) as Month, DATENAME(WEEKDAY,D.date) as DateName
--FROM @temp D
--Find weekend days in temp table, but don't count it if the @EnteredDate is on Saturday or Sunday
DECLARE @WeekendDays INT = (
SELECT RealWeekendDays - CASE
WHEN Datename(dw, @EnteredDate) = 'Sunday'
THEN 1
WHEN DateName(dw, @EnteredDate) = 'Saturday'
THEN 2
ELSE 0
END
FROM (
SELECT count(*) AS RealWeekendDays
FROM @temp
WHERE DATENAME(WEEKDAY, DATE) IN (
'Saturday'
,'Sunday'
)
) D
)
--Find public holidays
DECLARE @PublicHolidayCount INT = (
SELECT Count(*)
FROM [DB].dbo.PublicHolidays
WHERE DATE BETWEEN @WorkingStartDate
AND @EndDate
)
--Add missed days from weekend and public holidays to @EndDate
DECLARE @WorkingEndD DATETIME = (
SELECT DateAdd(day, (@WeekendDays + @PublicHolidayCount), @EndDate)
)
--In case @WorkinEndD ends on Saturday or Sunday, move it to Monday
DECLARE @WorkingEndDate DATETIME = (
SELECT DateAdd(day, CASE
WHEN DATENAME(WEEKDAY, @WorkingEndD) = 'Saturday'
THEN 2
WHEN DATENAME(WEEKDAY, @WorkingEndD) = 'Sunday'
THEN 1
ELSE 0
END, @WorkingEndD)
)
--Return outcome
RETURN (
SELECT @WorkingEndDate
)
END
GO
November 8, 2015 at 10:56 am
Quick question, can you post the DDL (create table script) for the table(s) used, some sample data as an insert statement and the expected results?
😎
Consider changing the function to a inline table value function as it performs in the order of the fourth magnitude better than the scalar function. At the first glance that should be straight forward.
November 8, 2015 at 7:30 pm
This is one of those places where a Calendar Table really shines. Here's a proc to build one (including standard USA holidays that my company observes). NOTICE that the proc drops a table called "dbo.Calendar" so don't use it if you already have one by that name. You can easily modify it to use holidays from your existing Holiday table to update it. I added the calculation for ISOYR today before I posted it.
ALTER PROCEDURE dbo.CreateCalendarTable
/**********************************************************************************************************************
Purpose:
Given a start and end year, create a Calendar table for those whole years plus the week before the start year and
the week after the end year to make certain calculations for the running workday calculations come out correctly.
The calendar table includes several workday calculations to exclude weekends and holidays.
The holidays are setup for the standard USA holidays except that Christmas Eve and New Year's Eve are considered
to be holidays because of the company that I currently work for.
Holiday Rules:
1. Christmas Eve and New Year's Eve are not considered to be holidays if they occur on the weekend.
2. Other holidays that occur on weekend are handled as follows:
a. If the holiday occurs on Saturday, the Friday before is considered to be a holiday.
b. If the holiday occurs on Sunday, the Monday after is considered to be a holiday.
c. Except for the holidays listed in Item #1 above, all holidays are calculated using current standard
definitions for the holidays. For example, holidays for things like New Year's day, Independence Day,
and Christmas day follow this rule #2.
3. Holidays such as Memorial Day (Last Monday of May), Labor Day (First Monday in September), and Thanksgiving Day
(4th Thursday of November) occur only on their assigned days. "Black Friday" is also considered to be a holiday.
Documentation for the columns will be included in the CTECH WIKI.
Usage:
Copy this proc to the database where you want the Calendar table to appear.
Run the proc as follows:
EXEC dbo.CreateCalendarTable @StartYear, @EndYear
where: @StartYear is the 4 digit start year in quotes such as '2013'
@EndYear >= @StartYear following the same format such as '2023'
Revision History:
Rev 00 - 17 Aug 2013 - Jeff Moden
- Initial creation
Rev 01 - 19 Aug 2013 - Jeff Moden
- Convert to a utility stored procedure
Rev 02 - 08 Nov 2015 - Jeff Moden
- Add ISOYr.
**********************************************************************************************************************/
--===== Declare the Input Parameters for this stored procedure.
@pStartYear DATETIME
, @pEndYear DATETIME
AS
--=====================================================================================================================
-- Presets
--=====================================================================================================================
--===== Environment
SET NOCOUNT ON;
--===== Declare some obviously named variables
DECLARE @StartDate DATETIME
, @EndDate DATETIME
, @TotalDays INT
;
--===== Preset the variables to make up for possible input errors and to establish date ranges for those inputs
-- to include the week before the start year and the week after the end year.
SELECT @pStartYear = DATEADD(yy,DATEDIFF(yy,0,@pStartYear),0) --Convert to begining of the year
,@pEndYear = DATEADD(yy,DATEDIFF(yy,0,@pEndYear),0) --Convert to begining of the year
,@StartDate = DATEADD(dd,-7,@pStartYear) --1 wk offset for WD# calculations
,@EndDate = DATEADD(dd,7,DATEADD(YYYY,1,@pEndYear)) --1 year, 1 wk offset so get whole year and WD# calcs.
,@TotalDays = DATEDIFF(dd,@StartDate,@EndDate) -- Figure out the # of days we have including the last day.
;
--=====================================================================================================================
-- Build the empty Calendar Table
--=====================================================================================================================
--===== Drop and rebuild the Calendar table just to make reruns in SSMS easier
IF OBJECT_ID('dbo.Calendar','U') IS NOT NULL
DROP TABLE dbo.Calendar
;
CREATE TABLE dbo.Calendar
(
DT DATETIME NOT NULL
,DTNext DATETIME NOT NULL
,DTInt INT NOT NULL
,YYYY SMALLINT NOT NULL
,MM TINYINT NOT NULL
,DD TINYINT NOT NULL
,DW TINYINT NOT NULL
,ISOYr SMALLINT NOT NULL
,ISOWk TINYINT NOT NULL
,MMM_YYYY CHAR(8) NOT NULL
,MMM CHAR(3) NOT NULL
,DDD CHAR(3) NOT NULL
,DWMonth TINYINT NOT NULL
,IsWorkDay TINYINT NOT NULL
,IsHoliday TINYINT NOT NULL
,WD#Prev INT NOT NULL
,WD#Next INT NOT NULL
,HolidayName VARCHAR(50) NOT NULL
)
;
--=====================================================================================================================
-- Add the basic date information to the Calendar Table
--=====================================================================================================================
--===== Build the basis of the Calendar table
WITH
cteDates AS
( --=== Create all of the dates we need using a CROSS JOIN as a row source
-- that I refer to as a "Pseudo-Cursor".
SELECT TOP (@TotalDays)
Date = DATEADD(dd,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,@StartDate)
FROM sys.all_columns ac1,
sys.all_columns ac2
),
cteDateParts AS
( --=== Calculate the most of the important date parts that we'll search on
SELECT DT = Date
,DTNext = DATEADD(dd,1,Date)
,DTInt = YEAR(Date)*10000 + MONTH(Date)*100 + DAY(Date)
,YYYY = YEAR(Date)
,MM = MONTH(Date)
,DD = DAY(Date)
,DW = DATEDIFF(dd,0,DATE)%7+1
,ISOYr = DATEPART(yy,DATEDIFF(dd,0,Date)/7*7+3)
,ISOWk = (DATEPART(dy,DATEADD(dd,DATEDIFF(dd,'17530101',Date)/7*7,'17530104'))+6)/7
,MMM_YYYY = REPLACE(SUBSTRING(CONVERT(CHAR(11),Date,113),4,8),' ','-')
,MMM = CONVERT(CHAR(3),Date,100)
,DDD = LEFT(DATENAME(dw,Date),3)
FROM cteDates
) --=== Calculate a few other date parts we couldn't calculate above and preset some rows we can't calculate yet.
INSERT INTO dbo.Calendar
SELECT DT,DTNext,DTInt,YYYY,MM,DD,DW,ISOYr,ISOWk,MMM_YYYY,MMM,DDD
,DWMonth = ROW_NUMBER() OVER (PARTITION BY YYYY,MM,DW ORDER BY DT)
,IsWorkDay = CASE WHEN DW IN (6,7) THEN 0 ELSE 1 END
,IsHoliday = 0 --We'll calculate this later
,WD#Prev = 0 --We'll calculate this later
,WD#Next = 0 --We'll calculate this later
,HolidayName = '' --We'll calculate this later
FROM cteDateParts
;
--=====================================================================================================================
-- Add the Holidays
--=====================================================================================================================
--===== New Year's Day (Specific Day)
UPDATE dbo.Calendar
SET HolidayName = 'New Year''s Day',
IsWorkDay = 0,
IsHoliday = 1
WHERE MM = 1
AND DD = 1
;
--===== New Year's Eve (Specific Day only on weekdays)
UPDATE dbo.Calendar
SET HolidayName = 'New Year''s Eve',
IsWorkDay = 0,
IsHoliday = 1
WHERE MM = 12
AND DD = 31
AND DW NOT IN (6,7)
;
--===== American Independence Day (Specific Day)
UPDATE dbo.Calendar
SET HolidayName = 'Independance Day',
IsWorkDay = 0,
IsHoliday = 1
WHERE MM = 7
AND DD = 4
;
--===== Memorial Day (Last Monday of May) could be 4th or 5th Monday of the month.
UPDATE dbo.Calendar
SET HolidayName = 'Memorial Day',
IsWorkDay = 0,
IsHoliday = 1
FROM dbo.Calendar
WHERE DT IN
(--=== Finds first Monday of June and subtracts a week
SELECT DATEADD(wk,-1,DT)
FROM dbo.Calendar
WHERE MM = 6
AND DW = 1
AND DWMonth = 1
)
;
--===== Labor Day (First Monday in September)
UPDATE dbo.Calendar
SET HolidayName = 'Labor Day',
IsWorkDay = 0,
IsHoliday = 1
WHERE MM = 9
AND DW = 1
AND DWMonth = 1
;
--===== Thanksgiving (4th Thursday in November)
UPDATE dbo.Calendar
SET HolidayName = 'Thanksgiving Day',
IsWorkDay = 0,
IsHoliday = 1
WHERE MM = 11
AND DW = 4
AND DWMonth = 4
;
--===== Thanksgiving Friday (The day after ThanksGiving)
UPDATE dbo.Calendar
SET HolidayName = 'Thanksgiving Friday',
IsWorkDay = 0,
IsHoliday = 1
WHERE DT IN
(--==== Finds ThanksGiving and adds a day
SELECT DATEADD(dd,1,DT)
FROM dbo.Calendar
WHERE MM = 11
AND DW = 4
AND DWMonth = 4
)
;
--===== Christmas (Specific Day)
UPDATE dbo.Calendar
SET HolidayName = 'Christmas Day',
IsWorkDay = 0,
IsHoliday = 1
WHERE MM = 12
AND DD = 25
;
--===== Christmas Eve (Specific Day only on weekdays)
UPDATE dbo.Calendar
SET HolidayName = 'Christmas Eve',
IsWorkDay = 0,
IsHoliday = 1
WHERE MM = 12
AND DD = 24
AND DW NOT IN (6,7)
;
--=====================================================================================================================
-- Update Holidays that occur on the weekend.
-- If the holiday occurs on Saturday, mark the Friday before as a holiday.
-- If the holiday occurs on Sunday, mark the Monday after as a holiday.
-- When either occurs, at the notation '(Observed)' to the holiday name.
--=====================================================================================================================
UPDATE cal
SET HolidayName = d.MovedHolidayName,
IsWorkday = 0,
IsHoliday = 1
FROM dbo.Calendar cal
INNER JOIN
(
SELECT MovedDate = CASE WHEN DW = 6 THEN DATEADD(dd,-1,DT) ELSE DATEADD(dd,+1,DT) END,
MovedHolidayName = HolidayName + ' (Observed)'
FROM dbo.Calendar
WHERE IsHoliday = 1
AND DW IN (6,7)
) d
ON cal.DT = d.MovedDate
;
--=====================================================================================================================
-- Now that all of the variable length columns have been calculated, build the clustered index and pack the
-- table as tightly as possible just to help performance a bit when the clustered index is used.
--=====================================================================================================================
ALTER TABLE dbo.Calendar
ADD CONSTRAINT PK_Calendar PRIMARY KEY CLUSTERED (DT) WITH FILLFACTOR = 100
;
--=====================================================================================================================
-- Calculate "running" workdays as a "WorkDayNumber" (WD#).
--=====================================================================================================================
--===== Calculate the running workday numbers excluding weekends and holidays
WITH
cteEnumerate AS
(
SELECT WD# = ROW_NUMBER() OVER (ORDER BY DT)
, WD#Prev
, WD#Next
FROM dbo.Calendar
WHERE IsWorkDay = 1
)
UPDATE cteEnumerate
SET WD#Prev = WD#,
WD#Next = WD#
;
--===== "Smear" the "last" workday numbers just prior to groups of non-workdays "down"
-- into the non-workdays. The update ripples back through both CTEs to pull this
-- off and it's very quick.
WITH
cteGroup AS
( --=== This creates groupings of the non-workdays by subtracting an ascending
-- number from the ascending non-workday dates. The grouping dates don't mean
-- anything... they just form groups of adjacent non-workdays.
SELECT DT
, PrevWorkDayGroup = DATEADD(dd,-ROW_NUMBER() OVER (ORDER BY DT),DT)
, WD#Prev
FROM dbo.Calendar
WHERE IsWorkDay = 0
),
cteDates AS
( --=== This numbers the dates within each group and then subtracts that number from the DT
-- column to come up with the last workday that occurred just before the non-workday group.
SELECT DT, PrevWorkDayGroup
, PrevWorkDayDate = DATEADD(dd,-DENSE_RANK()OVER(PARTITION BY PrevWorkDayGroup ORDER BY DT),DT)
, WD#Prev
FROM cteGroup
) --=== This joins the dates we came up with for each grouping above back to the calendar table
-- and updates the "workday" column with the related workday we find for each date.
UPDATE d
SET d.WD#Prev = c.WD#Prev
FROM cteDates d
JOIN dbo.Calendar c ON c.DT = d.PrevWorkDayDate
;
--===== "Smear" the "next" workday numbers just after groups on non-workdays "up"
-- into the non-workdays. The update ripples back through both CTEs to pull this
-- off and it's very quick.
WITH
cteGroup AS
( --=== This creates groupings of the non-workdays by adding an ascending
-- number to the descending non-workday dates. The grouping dates don't mean
-- anything... they just form groups of adjacent non-workdays.
SELECT DT
, NextWorkDayGroup = DATEADD(dd,ROW_NUMBER() OVER (ORDER BY DT DESC),DT)
, WD#Next
FROM dbo.Calendar
WHERE IsWorkDay = 0
)
,
cteDates AS
( --=== This numbers the dates within each group and then subtracts that number from the DT
-- column to come up with the "next" workday that occurred just after the non-workday group.
SELECT DT, NextWorkDayGroup
, NextWorkDayDate = DATEADD(dd,DENSE_RANK()OVER(PARTITION BY NextWorkDayGroup ORDER BY DT DESC),DT)
, WD#Next
FROM cteGroup
) --=== This joins the dates we came up with for each grouping above back to the calendar table
-- and updates the "workday" column with the related workday we find for each date.
UPDATE d
SET d.WD#Next = c.WD#Next
FROM cteDates d
JOIN dbo.Calendar c ON c.DT = d.NextWorkDayDate
;
--===== Indexes added after the fact and based only on current needs.
CREATE INDEX IX_WD#ext ON dbo.Calendar (WD#Next) INCLUDE (DT);
Once you've created that proc, you can generate all the dates by calling it with two year parameters. For example, the following call generates the Calendar table for a week before 2000-01-01 and a week after 2050-12-31. Obviously, you need to do that only once for any given company.
EXEC dbo.CreateCalendarTable '2000', '2050';
My normal recommendation to people is to delete any columns that they don't actually need and then rebuild the clustered index on the table to actually remove the columns and recover the space, which also makes queries against the table faster because there will be more rows per page and, thus, fewer reads per query.
There are a couple of very special columns in this table. WD#Next numbers days on the weekends/holidays the same as the next business day. WD#Prev numbers days on the weekends/holidays the same as the previous business day.
The WD#Next column is just what we need for your problem and turns it into child's play.
--===== These would be the parameters of an ITVF
DECLARE @pDate DATETIME
,@pWorkingDays INT
;
SELECT @pDate = '11/7/2015' --Your date from your code, assuming it's MM/DD/YYYY
,@pWorkingDays = 11
;
--===== Find the last working day according to @pworkingDays skipping weekends and holidays.
SELECT DT = MAX(DT)
FROM dbo.Calendar
CROSS APPLY (SELECT WD#Next FROM dbo.Calendar WHERE DT = @pDate) ca (WDNext)
WHERE WD#Next = ca.WDNext + @pWorkingDays - 1
;
I strongly recommend that if you're going to use it on a regular basis that you convert the code immediately above into an iTVF (Inline Table Valued Function) even though it returns only a single value. The following article explains why and how. As Eirikur suggests, scalar UDFs are rotten for speed.
How to Make Scalar UDFs Run Faster[/url]
--Jeff Moden
Change is inevitable... Change for the better is not.
November 9, 2015 at 9:39 am
Thanks Jeff! I will give it a try and let you know the result.
November 10, 2015 at 10:53 pm
Wow, it works perfect! What a great solution! Thanks a lot.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply