Calculating the difference between two dates

Question

I have a report that calculates multiple date differences (in business days, not DATEDIFF) for a variety of business reasons that are far too dull to get into.

Basically the query (right now) looks something like

SELECT -- some kind of information
       DATEDIFF(dd, DateOne, DateTwo) AS d1_d2_diff,
       DATEDIFF(dd, DateOne, DateThree) AS d1_d3_diff,
       DATEDIFF(dd, DateTwo, DateThree) AS d2_d3_diff,
       DATEDIFF(dd, DateTwo, DateFour) AS d2_d4_diff
  FROM some_table;

I could change this calculation to use a scalar function, but I don't want the scalar function to be executed 4 times for every row in the result set.

I have a Calendar table in the database:

CREATE TABLE Calendar (
   Date DATETIME NOT NULL,
   IsWeekday BIT,
   IsHoliday BIT
);

Would a table-valued function and CROSS APPLY be a good choice here? If so, how would I go about writing such a thing? Or is a scalar function my best bet?

Important Note All date values in our database have been stripped of time so it is safe to ignore any code that would reset days to midnight.

Answer 1

Realistically I think you want to go with a scalar function for this. At first glance you are going to need to do a few calculations. Then I thought about it more, and you can actually do this quite simply with a two step process.

1.) Roll your date values back to midnight of the respective days, that way you can easily figure it out. Due to extra information provided, this is not needed!

2.) Execute a query to find out how many week days, that are not holidays exist between the day values

SELECT ISNULL(COUNT(*), 0)
FROM Calendar
WHERE [DATE] > DateOne 
    AND [DATE] < DateTwo
    AND IsWeekDay = 1
    AND IsHoliday = 0

Overall I think that the most efficient way is to just do this as a Scalar Function, I'm sure there might be other ways, but this way is straightforward, and as long as you have an index on the Calendar table it shouldn't be too bad performance wise.

note on cross apply

Doing a bit of looking, this could also be done via cross apply, but really in the end it does the same thing, so I think that the Scalar function is a better solution as it is easier to understand, and is easily repeatable.

Answer 2

I too would suggest that you use a scalar function for this. Below is such a function that I stole from here. With that you only need to maintain a table of holidays and subtract the number that fall between your start and end date.

CREATE FUNCTION dbo.fn_WeekdayDiff(@StartDate DATETIME, @EndDate DATETIME)
RETURNS INT
AS
--Calculdate weekdays between two dates
BEGIN
    --if @StartDate is AFTER @EndDate, swap them
    IF @StartDate > @EndDate
    BEGIN
     DECLARE @TempDate DATETIME
     SET @TempDate = @StartDate
     SET @StartDate = @EndDate
     SET @EndDate = @TempDate
    END

    RETURN
     --number of weeks x 5 weekdays/week
       (DATEDIFF(ww, @StartDate, @EndDate) * 5)
     --add weekdays left in current week
     + CASE DATEPART(dw, @StartDate + @@DATEFIRST) WHEN 1 THEN 5 ELSE (7 - DATEPART(dw, @StartDate + @@DATEFIRST)) END
     --subtract weekdays after @EndDate
     - dbo.fn_MaxInt(6 - DATEPART(dw, @EndDate + @@DATEFIRST), 0)
END

Answer 3

The trick is using an inline table valued function, since they don't suffer the same performance penalty as a scalar function. They are equivalent to actually pasting the source code of the function right into the query.

Here's how it works:

create function BusinessDayDiffs_fn ( 
  @DateOne datetime
, @DateTwo datetime
)
returns table
as return (
  select count(*) as numBusinessDays
  from Calendar
  where date between @DateOne and @DateTwo
    and IsWeekday = 1
    and IsHoliday = 0;
)

GO

select
  d1_d2_diff = d1_d2.numBusinessDays,
  d1_d3_diff = d1_d3.numBusinessDays,
  d2_d3_diff = d2_d3.numBusinessDays,
  d3_d4_diff = d3_d4.numBusinessDays
from some_table s
cross apply BusinessDayDiffs_fn( DateOne, DayTwo  ) d1_d2
cross apply BusinessDayDiffs_fn( DateOne, DayThree) d1_d3
cross apply BusinessDayDiffs_fn( DayTwo,  DayThree) d2_d3
cross apply BusinessDayDiffs_fn( DayTwo,  DayFour ) d2_d4;

This should perform pretty well, as it's the same as taking the subquery out of the function, and pasting it right into the select clause of the main query. It'll be WAY faster than the scalar function.

Answer 4

Below is a version based on the above that should work for MySQL

#
# This function calculates the total number of weekdays (inclusive)
# between the specified dates.
#
# If start date < end date, the value returned is negative
#
# Known issues - due to the inaccuracy of the MySQL WEEK detection
# boundaries across years may be incorrect
#

DELIMITER $$

DROP FUNCTION IF EXISTS `dbname`.`WeekdayDiff` $$
CREATE FUNCTION `dbname`.`WeekdayDiff` (start_date date, end_date date) RETURNS INT DETERMINISTIC
BEGIN
  DECLARE week_diff INT;
  DECLARE week_diff_add_days INT;
  DECLARE temp_date DATE;
  DECLARE multiplier INT;
  DECLARE wd_left_in_start_inclusive INT;
  DECLARE wd_left_in_end_exclusive INT;
  DECLARE wd_diff INT;

  SET multiplier = 1;

  IF start_date > end_date THEN
    SET temp_date = end_date;
    SET end_date = start_date;
    SET start_date = temp_date;
    SET multiplier = -1;
  END IF;

  # Note we subtract 1 from the dates here as
  # we want sunday to be included in the last week
  SET week_diff = (YEAR(end_date) * 52 + WEEK(end_date-1)) - (YEAR(start_date) * 52 + WEEK(start_date-1));
  SET week_diff_add_days = week_diff * 5;

  # Calculate the week days left in the start week
  SET wd_left_in_start_inclusive = GREATEST( 5 - WEEKDAY( start_date ), 0 );
  SET wd_left_in_end_exclusive = GREATEST( 4 - WEEKDAY( end_date ), 0 );

  SET wd_diff = week_diff_add_days + wd_left_in_start_inclusive - wd_left_in_end_exclusive;

  RETURN wd_diff * multiplier;
END $$

DELIMITER ;