Many date and time calculations in SQL can be done very efficiently using the built-in functions. However, when it comes to calculating the next or previous working day (or n working days from a date) – a pretty regular requirement in my experience – we can’t rely on SQL’s built in date functions. There are two reasons for this. Firstly, SQL doesn’t know about public holidays in the region you are referring to, and secondly, even if we are willing to ignore public holidays, not every country in the world works Monday to Friday.
Both these issues can be resolved by using two tables:
tblCalendar stores the typical work-week for each region of interest, and tblHoliday stores all the public holidays as far back or forward as you want to go for each line in tblCalendar. This provides us with enough information to calculate whether any given day is a work-day or not. But to efficiently calculate n work-days from a given day we need to use a third table, tblDay, to store pre-calculated data regarding whether any given day is a work-day in each calendar.
- tblCalendar – CalendarID, CalendarName, Workweek
- tblHoliday – HolidayID, CalendarID, Date
- tblDay – DayID, CalendarID, Date, IsWorkingDay, ForwardCount, BackCount
tblCalendar’s Workweek will typically be “YYYYYNN”, meaning Monday to Friday are work days and Saturday and Sunday are not, but obviously can be anything.
tblHoliday shouldn’t be too controversial. The HolidayID is not used in any of the system described here, but every table should have an ID column. Always.
tblDay is largely self-explanatory. The tricky part is ForwardCount and BackCount. They are the basis of the whole system, and should be explained.
To calculate the 4th working day after Thurs 22nd December, we get the ForwardCount (10501), then return the Date where ForwardCount = 10501 + 4 and IsWorkingDay = 1.
To calculate previous working days, use BackCount rather than ForwardCount, but it is essentially the same process. The reason we need to have both ForwardCount and BackCount is for cases where the start date is a non-working day. It is clear if you look at the table that if you start on Tues 27 Dec and try to find the previous working day using the ForwardCount column that you’ll get to Thurs 22 Dec.
The ForwardCount and BackCount columns are unique. I mean that the numbers are calculated from a starting point of CalendarID * 100,000 – I’m guessing you don’t need to calculate working days more than 100,000 days (about 300 years) in the past or future. If you do, feel free to make it 1000,000 or whatever you want. Anyway, what this amounts to is that CalendarID 1 starts at 100,000 and CalendarID 2 starts at 200,000. (Yes, I know this doesn’t agree with the screenshot above – I splashed out on an extra zero after making that)
Before calculating the ForwardCount and BackCount we need to calculate IsWorkingDay. There are two or three complications here. Obviously public holidays vary from place to place. Also, as mentioned above, not every country in the world has a Mon-Fri work week. Finally, and this could be a real trap if you’re not careful, the value of the @@DATEFIRST system variable can vary, and indeed does by default vary per region. This variable indicates what day is the first day of the week. Most people would consider Monday or Sunday to first, but it could be anything, and even within those two, which is it?
So to make this whole system work robustly we need to be confident that local system settings are not going to catch us out.
To get the day of the week of @date with Sunday being the first day use
SELECT (@@DATEFIRST + DATEPART(dw, @date))%7
However, we want Monday to be the first day, and Sunday to be #7.
SELECT 1+(@@DATEFIRST - 2 + DATEPART(dw, @date))%7
The complete function looks like this:
CREATE FUNCTION [dbo].[DayOfWeek](@date AS Date) RETURNS TINYINT AS BEGIN DECLARE @return AS TINYINT SELECT @return = 1+(@@DATEFIRST - 2 + DATEPART(dw, @date))%7 RETURN @return END
We can now use our DayOfWeek function combined with the Workweek field from tblCalendar (something like ‘YYYYYNN’) to check for weekends. If the day is a weekend day we return zero, otherwise we return 1.
If the day is not a weekend, then we check to see if there is a holiday in tblCalendar for that day and that CalendarID. If not, we return 1, otherwise we return zero.
CREATE FUNCTION [dbo].[IsWorkingDay] (@date DATE, @calendarID INT) RETURNS BIT AS BEGIN DECLARE @workWeek VARCHAR(7) DECLARE @workDay VARCHAR DECLARE @isWeekend BIT DECLARE @return BIT SELECT @workWeek = cal.Workweek FROM dbo.tblCalendar cal WHERE cal.CalendarID = @calendarID SELECT @workDay = SUBSTRING(@workWeek, dbo.DayOfWeek(@date), 1) IF @workDay = 'N' SELECT @return = 0 ELSE BEGIN SELECT @return = 1 SELECT @return = 0 FROM tblHoliday hol WHERE hol.CalendarID = @calendarID AND hol.[Date] = @date END RETURN @return END
To populate tblDay for a given calendar, we need to know the holiday dates (yep, I know that should be obvious). After populating tblCalendar and tblHoliday with dates for the next and previous few years you’re ready to use the proc spUpdateTblDay. For each day we calculate whether it is a working day, then after doing this for every day in the period of interest, we can calculate ForwardCount and BackCount. All this is done using the Stored Proc spUpdateTblDay (I was up all night thinking up that name!).
CREATE PROC [dbo].[spUpdateTblDay](@calendarID as int, @startDate as date, @endDate as date) AS BEGIN --Populates tblDay with working day and ForwardCount/BackCount data --to allow for quick next-working-day type calculations --Forward and Back Counts have to be done separately to allow for the --case when we are calculating the next working day from e.g. a --Saturday. The Saturday would have the same forwardID as the Friday, --but the same BackID as the Monday. /* declare @startDate DATE declare @endDate DATE declare @calendarID INT set @startDate = '1 January 2010' set @endDate = '31 December 2012' set @calendarID = 1 */ declare @forwardCount BIGINT declare @backCount BIGINT declare @forwardStart BIGINT declare @backStart BIGINT --100,000 days is roughly 300 years. Unlikely to need to calculate --working days more than that far in the past or future. SET @forwardStart = 100000 * @calendarID SET @backStart = 100000 * @calendarID DELETE FROM tblDay WHERE CalendarID = @calendarID DECLARE @date DATE SET @date = @startDate WHILE @date <= @endDate BEGIN INSERT INTO tblDay(CalendarID, Date, IsWorkingDay, ForwardCount, BackCount) VALUES(@calendarID, @date, dbo.IsWorkingDay(@date, @calendarID), 0,0) SET @date = dateadd(d, 1, @date) END ---********FORWARD COUNT*********--- UPDATE tblDay SET tblDay.ForwardCount = f.forwardCount FROM tblDay JOIN ( SELECT @forwardStart + SUM(CONVERT(INT,td.IsWorkingDay)) AS forwardCount, d.CalendarID, d.Date FROM tblDay td JOIN tblDay d ON d.CalendarID = td.CalendarID AND d.Date >= td.Date GROUP BY d.CalendarID, d.Date) AS f ON tblDay.CalendarID = f.CalendarID AND tblDay.Date = f.Date WHERE tblDay.CalendarID = @calendarID ---********BACK COUNT*********--- UPDATE tblDay set tblDay.BackCount = f.backCount from tblDay JOIN ( SELECT @backStart + SUM(CONVERT(INT,td.IsWorkingDay)) AS backCount, d.CalendarID, d.Date FROM tblDay td JOIN tblDay d ON d.CalendarID = td.CalendarID AND d.Date <= td.Date GROUP BY d.CalendarID, d.Date) AS f ON tblDay.CalendarID = f.CalendarID AND tblDay.Date = f.Date WHERE tblDay.CalendarID = @calendarID END GO
Finally we get to the point of the whole exercise: calculating n working days before or after a given day. The basic logic was described earlier in this post. We need to pass in three parameters: a date, a number of days to count forwards or backwards, and a calendarID. If the number of days is zero, we just return the date passed in. If the date passed in is a working day, this makes sense anyway. If it is not a working day (e.g. a Sunday), then you could equally well argue that we should skip forward to Monday, fall back to Friday, or stay where we are. Returning either Monday or Friday in that case would seem to me to be pretty arbitrary. At least returning Sunday has a certain logic to it:
- Q: “What is zero working days from Sunday?”
- A: Sunday.
CREATE FUNCTION [dbo].[GetWorkingDay]( @date AS DATE, @days AS INT, @calendarID AS INT) RETURNS DATE AS BEGIN /* DECLARE @date DATE DECLARE @days INT DECLARE @calendarID set @date = '25 DECEMBER 2011' set @days = 2 set @calendarID = 1 */ ----- DECLARE @return DATE DECLARE @dayID BIGINT --If @days = 0, return @date, even if it is a non-working day SELECT @return = @date IF @days > 0 BEGIN SELECT @return = Date FROM tblDay WHERE IsWorkingDay = 1 AND ForwardCount = ((SELECT ForwardCount FROM tblDay t2 WHERE t2.Date = @date AND @calendarID = t2.CalendarID) + @days) END IF @days < 0 BEGIN SELECT @return = Date FROM tblDay WHERE IsWorkingDay = 1 AND BackCount = ((SELECT BackCount FROM tblDay t2 WHERE t2.Date = @date AND @calendarID = t2.CalendarID) - @days) END RETURN @return END
Show me the SQL!
Everything I’ve described above is downloadable as a zip file.
See my next post for a discussion of indexing to improve performance of this system.
Let me know if you have any questions/comments.