Business Days Between
This post reviews a custom SQL function that counts the number of business days between two dates. In analytics, there are instances when the number of business days between two dates, rather than just total days, is a valuable metric to consider. Relevant use cases can include performance metrics or turnaround times for entities like banks or government agencies. If non-business days obfuscate what we’re trying to measure, this function can help.
We’ll review a use case, then go over the code. The code can work for individual records, or on an aggregate level. Below are summary statistics taken from San Francisco’s 311 data. This data covers government responses to 311 requests from the public, aka non-emergency requests for help. We’ll look at the average time to close cases: “all days” vs “just business days”. The data is filtered for closed cases that were opened between 2021–11–01 through 2022–01–31. It’s further filtered to look at the top three graffiti related cases:
We see in this example that there is a big difference in average time to close for all days vs just business days, a full week. From a data content perspective, this makes the data set a good one to use as an example for the between business days function. However, whether all days or just business days (or both) is the correct look for your analytics depends on the rules and facts driving the subject matter foundation of your data set.
For our example, we’ll consider days to close a primary KPI (key performance indicator) for 311 graffiti case analysis. We’ll also imagine that the people working to resolve graffiti claims don’t work on weekends or holidays. If they did, and we wouldn’t expect a change in performance during weekends or holidays, then simply looking at all days between the open and close dates is likely the right metric. As these cases can’t be worked on non-business days (in our example), a days to close calculation that counts just business days has value for us.
Now that we have a use case set up, we can move to the code. We will use a SQL function that takes advantage of having a calendar table available in your database. This post covers setting up a calendar table in SQL. Once we have the calendar table available, we can set up our UDF (user-defined function). Our function takes three parameters: start date, end date, and whether the range should be exclusive or inclusive of the end date.
This function simply does math off of our calendar table. The values we pass to the date parameters set the boundaries for the sum equation the function uses to do that math. The last parameter determines if the boundary will exclude, ‘exc’, or include, ‘inc’, the end date we pass to its sum calculation (to the end date, or through the end date). Note, you can actively pass null for the last parameter, it would be treated as excluding the end date from the calculation. After adding the function to your local environment, you can include it in your query like so:
The query produces this result set:
We’re using a time frame comprised of a high number of non-business days, so the difference in results is drastic and, hopefully, illustrative. Below is a depiction of how the different calculations are coming to their respective results for the first case (14749453). Note, DATEDIFF() doesn’t count days, it counts thresholds passed between two dates (let’s not get caught up here, shall we!):
In the first example, the exclusive vs inclusive parameter produces the same result. That’s because the close date is a holiday, so it won’t be counted in either instance (MLK Day 2022). The exclusive or inclusive parameter does create a different result in the next case, 14749495:
We can see from the individual cases that the difference in days to close for all days vs just business days can vary greatly. If we’re using this metric to evaluate resolution time, and we can’t make valid progress closing cases on non-business days, then we likely want to include a just business days time to close calculation as one of our evaluation metrics. Doing so may provide valuable insight into performance.
To use our business days between UDF as a summary statistic, we can wrap it in an aggregate function. Here, we calculate the average business days between open and close dates for our data set:
Last, we’ll chart out average days to close by case opened date:
From the graph, we can see that the difference in resolution time is fairly consistent. We also see that there’s less variance in the business days between metric compared to total days. This is true whether we look at variance week over week, or max vs min. These differences may provide helpful insights or comparison points. The value of these insights is tied to the subject matter particularities of your data set.
