Blog

Counting Work Days

holidays aren't work days

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:

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:

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.

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:

This is just a simple insert statement to begin with, then it gets fun:

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.

Now it is time to update the work days.

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.

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:

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.

 

facebooktwittergoogle_plusredditpinterestlinkedinmailfacebooktwittergoogle_plusredditpinterestlinkedinmail

One Comment

Leave Reply

You must be logged in to post a comment.