计算两个日期之间的工作日数-T-SQL?

我意识到不同的解决方案会有不同的“工作日”的含义,但在我的例子中,我的意思是星期一到星期五。

基本上,我已经创建了一个函数来为我进行计算,并且我当前的解决方案是有效的。我的担心(和问这个问题的原因)是我担心这是一个实现这一点的糟糕方法,因为函数被调用的频率非常高。在过去的3个月里,生产系统的平均工作时间为44ms,被称为1200万次。

这让我怀疑这是否是实现解决方案的正确方法。

首先是我创建的函数:

 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

作为使用它的简单示例,我将运行这种类型的查询:

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

MyTable可以包含5000行,所有行在EntryDate列中都有不同的日期(对函数的5000次调用)

我的问题是,我在这里做这件事的方式遗漏了一些东西,是否有利于为此创建一个查阅表格(但这是许多日期组合)

如有任何想法、改进或建议,我们将不胜感激…