Calculating how many Working Days between 2 Dates - T-SQL?

Question

I realise different solutions will have different variations of what "Working Days" means but in my case I mean Monday to Friday inclusive.

Basically I have Created a function to do the calculation for me and my current solution works. My concern (and reason for asking this question) is that I am worried that this is a bad way of achieving this because the function is being called with a very high frequency. In the last 3 months it has been called 12 million times on a production system, with the average worker time 44ms.

This lead me to wonder if this is the correct way of achieving solution.

Firstly here is the function I created:

 CREATE FUNCTION [dbo].[fn_WorkDays]
    (
     @StartDate DATETIME,
     @EndDate   DATETIME = NULL --@EndDate replaced by @StartDate when DEFAULTed
    )
    RETURNS INT
 AS

 BEGIN
    --===== Declare local variables
    --Temporarily holds @EndDate during date reversal
    DECLARE @Swap DATETIME

    --===== If the Start Date is null, return a NULL and exit
         IF @StartDate IS NULL
            RETURN NULL

    --===== If the End Date is null, populate with Start Date value
         -- so will have two dates (required by DATEDIFF below)
         IF @EndDate IS NULL
            SELECT @EndDate = @StartDate

    --===== Strip the time element from both dates (just to be safe) by converting
         -- to whole days and back to a date.  Usually faster than CONVERT.
         -- 0 is a date (01/01/1900 00:00:00.000)
     SELECT @StartDate = DATEADD(dd,DATEDIFF(dd,0,@StartDate),0),
            @EndDate   = DATEADD(dd,DATEDIFF(dd,0,@EndDate)  ,0)

    --===== If the inputs are in the wrong order, reverse them
         IF @StartDate > @EndDate
            SELECT @Swap      = @EndDate,
                   @EndDate   = @StartDate,
                   @StartDate = @Swap

    --===== Calculate and return the number of workdays using the
         -- input parameters.  This is the meat of the function.
         -- This is really just one formula with a couple of parts
         -- that are listed on separate lines for documentation
         -- purposes.
     RETURN (
            SELECT
          --Start with total number of days including weekends
            (DATEDIFF(dd,@StartDate,@EndDate)+1)

          --Subtact 2 days for each full weekend
           -(DATEDIFF(wk,@StartDate,@EndDate)*2)

          --If StartDate is a Sunday, Subtract 1
           -(CASE WHEN DATENAME(dw,@StartDate) = 'Sunday'
                  THEN 1
                  ELSE 0
              END)

          --If EndDate is a Saturday, Subtract 1
           -(CASE WHEN DATENAME(dw,@EndDate) = 'Saturday'
                  THEN 1
                  ELSE 0
              END)
            )
END

As a simple example of its use I would run this type of query:

SELECT MYTABLE.EntryDate
     ,dbo.fn_WorkDays(MYTABLE.EntryDate, getutcdate()) as WorkingDays
    FROM MYTABLE                                    

MyTable could contain 5000 rows all with different Dates in the EntryDate Column (5000 calls to Function)

My question is I am missing something here in the way that I am doing this, would it be beneficial to create a lookup table for this (but that is a lot of combinations of dates)

Any thoughts, improvements or recommendations would be appreciated...

Answer 1

There's two problems here:

1. Calculating the number of days between two dates,
2. Identifying whether or not a give Date is a "business day".

The second includes easy ones like "weekday" versus "weekend", holidays (secular, religious, and legal), etc.

You'll need to solve both.

The first is easier, because relational databases will have functions to help you. It's the second that's harder and more variable, because it changes by locale and business.

Answer 2

I don't think there's a lot you can do with the UDF tbh - having it calculated at run-time like this in SQL is always going to incur a hit to some degree.

So, ideally (and this may not be possible as I don't know the full picture), I think what I'd do is store the WorkingDays number in your table and calculate it ONCE when the record is created. If that's not possible (i.e. when the record is created, you don't have an "end date" so it has to be worked out using "now") then I'd be considering a nightly scheduled job to go and recalculate all those particular records so that they are updated each day - then when an "end date" does get entered, that record does not get included in this batch update.

The benefits of this, are you offload the calculations to a quieter period, and only do the calculations once per day. The query becomes a lot simpler and more performant as it can just read the WorkingDays number from the table.

If that's not an option, then I'd suggest doing the calculations in the front end, remove the hit from the DB.