计算两个日期之间的差异

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.

我有一份报告，它计算出了各种各样的日期差异(在工作日，而不是DATEDIFF)，因为各种各样的商业原因都太枯燥了，无法进入。

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.

我可以将这个计算改为使用标量函数，但我不希望结果集中的每一行执行4次标量函数。

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.

重要的是，我们的数据库中的所有日期值都被剥夺了时间，所以忽略任何将会重置到午夜的代码是安全的。

4 个解决方案

#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!

1)。将您的日期值滚回相应日期的午夜，这样您就可以很容易地找到它。由于提供了额外的信息，这是不需要的!

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

2)。执行一个查询来找出多少个工作日，这不是假日之间存在的日值。

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.

稍微看一下，这也可以通过交叉应用来完成，但实际上最后它也做了同样的事情，所以我认为标量函数是一个更好的解决方案，因为它更容易理解，也更容易重复。

#2

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.

这应该执行得很好，因为这与从函数中取出子查询并将其粘贴到主查询的select子句中是一样的。它会比标量函数快很多。

#3

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

#4

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

下面是基于上面的版本，应该适用于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 ;

#1