Don’t forget that most holiday’s aren’t work days.
Recently while working with a client I was faced with some seriously convoluted code to count the number of days between a couple of dates.
Usually this is not a big issue, a simple datediff() works, but this also had to take into account customized options for their application.
Options such as excluding the whole weekend, just Sunday or including weekends as work days. Oh, and there are company defined holidays to add to the mix.
Can’t forget them.
The code being reviewed was performing all sorts of look ups and calculations to figure out how to remove the appropriate days from the count of days between the dates.
I pondered over this for a while and after finding a great response on stackoverflow from Aaron Bertrand (T|B) I was able to come up with the following solution to help with performance and to help simplify the coding.
I was going to just take a table and populate it with all the days that could be in the ranges being looked at; in this case the year 2000 through 2030.
Once the table was populated, I would then go back and based on what criteria are required, mark certain dates as non-workdays.
It sounded simple, and it turns out it kind of was.
The client already had a holidays table that the client would input all the company holidays into.
The table is very simple and here’s the code to make a similar table:
CREATE TABLE dbo.Holidays
(
HolidayID INT IDENTITY(1, 1) NOT NULL ,
HolidayDate DATETIME NOT NULL
CONSTRAINT pk_Holidays_idHolidays PRIMARY KEY NONCLUSTERED ( HolidayID ASC )
)
GO
There was also a value in the applications configuration to either include weekends (0 – zero), include Sunday only (1) or exclude Saturday and Sunday (2).
For this I added a look up to the configuration table, but if you are always excluding or including certain days you could just as easily hard code the condition into the procedure I created as noted later.
To begin though I needed a table to hold all the dates. I opted to name it Calendar and here is the code that created it:
CREATE TABLE dbo.Calendar ( dt DATE PRIMARY KEY, -- use SMALLDATETIME if < SQL Server 2008 IsWorkDay BIT );
There are only 2 fields that I really needed in the calendar table. The date and a flag to indicate if the day is a ‘workday’
Once the table was created, it was time to populate it.
There are many ways to do this, but I wanted some extra flexibility if the table had to be reloaded or changed to include more dates, so I created the following stored procedure.
CREATE PROCEDURE dbo.Populate_Calendar (@StartDt DATE, @EndDt DATE) AS
/****************************************************************************
Desc: procedure to populate the dbo.Calendar table
-----------------------------------------------------------------------------
Created 9-19-2013 by WaterOx Consulting,Inc (http:\\www.wateroxconsulting.com)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
For Testing only!
DECLARE @StartDt DATE ,
@EndDt DATE;
SELECT @StartDt = '2000-01-01' ,
@EndDt = '2029-12-31';
****************************************************************************/
-- Empty the calendar table and repopulate with all dates in range provided
TRUNCATE TABLE dbo.Calendar
-- Populate the tablewith all dates between the specified date parameters
INSERT dbo.Calendar
( dt ,
IsWorkDay
)
SELECT DATEADD(DAY, n - 1, @StartDt) ,
1
FROM ( SELECT TOP ( DATEDIFF(DAY, @StartDt, @EndDt) + 1 )
ROW_NUMBER() OVER ( ORDER BY s1.[object_id] )
FROM sys.all_objects AS s1
CROSS JOIN sys.all_objects AS s2
) AS x ( n );
-- Set the first day of the week to Monday. This only affects the current session and is not an overall global setting.
-- Bonus FYI: the DateDiff function will always use Sunday as the first day regardless of this setting to ensure the function is deterministic
SET DATEFIRST 1;
-- If the weekend are excluded the set them now as non-workdays
-- This was custom coded for the client's application - change it as you need or just hardcode the value
DECLARE @wkends TINYINT
SELECT @WkEnds = WkEndDays
FROM dbo.SystemDefaults;
-- Excludes Saturday and Sunday
IF @WkEnds = 2
BEGIN
UPDATE dbo.Calendar
SET IsWorkDay = CASE WHEN DATEPART(weekday, dt) IN ( 6, 7 ) THEN 0
ELSE 1
END
END
-- Exclude Sundays only
IF @WkEnds = 1
BEGIN
UPDATE dbo.Calendar
SET IsWorkDay = CASE WHEN DATEPART(weekday, dt) = 7 THEN 0
ELSE 1
END
END
-- Include Weekends
-- This is redundant since the initial population of the table defaulted every day to a work day, but it was put in "just in case"
IF @WkEnds = 0
BEGIN
UPDATE dbo.Calendar
SET IsWorkDay = 1
END
-- Set the company holidays specified in the configuration table as non-workdays
UPDATE dbo.Calendar
SET IsWorkDay = 0
WHERE dt IN ( SELECT CONVERT(DATE, HolidayDate)
FROM dbo.Holidays )
Lets go through this procedure real quick:
First it truncates the Calendar table so we can start with a clean slate.
Then a simple insert is performed.
The breakdown of the insert section is as follows:
INSERT Base.Calendar ( dt , IsWorkDay )
This is just a simple insert statement to begin with, then it gets fun:
SELECT DATEADD(DAY, n - 1, @StartDate) ,
1
FROM ( SELECT TOP ( DATEDIFF(DAY, @StartDate, @EndDate) + 1 )
ROW_NUMBER() OVER ( ORDER BY s1.[object_id] )
FROM sys.all_objects AS s1
CROSS JOIN sys.all_objects AS s2
) AS x ( n );
By using the cross join to the sys.all_objects table and calculating the row_number() for the number of days between the start and end date a sequential list of numbers from 1 to the number of days is created. The “as x ( n )” portion returns the value on each row to the outer select statement where the number of days is added to the @startdate parameter value to generate a date. This date is then inserted to our Calendar table. The 1 value is being used to hard code each day as a work day.
Next the session is configured to treat Monday as the first day of the week. This just makes it easier to tag Saturday or Sunday as 6 & 7 rather than SQL’s default of using 1 & 7.
A nice thing with SET DATEFIRST is that it is for the session only. Do note: It will not affect the DateDiff function. That always has Sunday as the first date to maintain its deterministic nature.
SET DATEFIRST 1;
Now it is time to update the work days.
DECLARE @wkends TINYINT
SELECT @WkEnds = WkEndDays
FROM dbo.SystemDefaults;
-- Excludes Saturday and Sunday
IF @WkEnds = 2
BEGIN
UPDATE dbo.Calendar
SET IsWorkDay = CASE WHEN DATEPART(weekday, dt) IN ( 6, 7 ) THEN 0
ELSE 1
END
END
-- Exclude Sundays only
IF @WkEnds = 1
BEGIN
UPDATE dbo.Calendar
SET IsWorkDay = CASE WHEN DATEPART(weekday, dt) = 7 THEN 0
ELSE 1
END
END
-- Include Weekends
-- This is redundant since the initial population of the table defaulted every day to a work day, but it was put in "just in case"
IF @WkEnds = 0
BEGIN
UPDATE dbo.Calendar
SET IsWorkDay = 1
END
In my situation I had a table that could be modified to change the number of weekend days to be excluded.
This section basically checked the value in the configuration table and then changed the workday indicator in the calendar table appropriately.
I was redundant with the include weekends section as the initial insert of the dates defaulted them all to workdays. I am pretty sure it was subconscious DBA paranoia that made me do that.
Finally it was time to mark the holidays. This was done with a quick update of the dates that were in the Holiday table the client’s application had.
UPDATE dbo.Calendar
SET IsWorkDay = 0
WHERE dt IN ( SELECT CONVERT(DATE, HolidayDate)
FROM dbo.Holidays )
Overall this populated the table very quickly and the size was fairly small.
To use the table to count the number of days you only need to count records in the calendar table for the date range being looked at where the isworkday flag is marked.
Something along the lines of this:
SELECT COUNT(1)
FROM base.Calendar
WHERE dt BETWEEN @ldDateOrdered AND @ldDateDelivered
AND isworkday = 1
Again, huge thanks to Aaron Bertrand (T|B) for providing the start of a nice solution to someone else that helped me, my client and will hopefully help you.