计算两个日期之间有多少个工作日 - T-SQL? [英] Calculating how many Working Days between 2 Dates - T-SQL?

问题描述

我意识到不同的解决方案会对工作日"的含义产生不同的影响，但就我而言，我指的是周一至周五.

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

基本上我已经创建了一个函数来为我进行计算并且我当前的解决方案有效.我的担忧(以及提出这个问题的原因)是我担心这是实现这一目标的糟糕方法，因为该函数的调用频率非常高.在过去 3 个月内，它在生产系统上被调用了 1200 万次，平均工作时间为 44 毫秒.

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 可以包含 5000 行，所有行都在 EntryDate 列中具有不同的日期(5000 次函数调用)

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...

推荐答案

我不认为你可以用 UDF tbh 做很多事情 - 在 SQL 中像这样在运行时计算它总是会导致受到一定程度的打击.

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.

所以，理想情况下(这可能是不可能的，因为我不知道全貌)，我认为我要做的是将 WorkingDays 数字存储在您的表中，并在创建记录时计算一次.如果这是不可能的(即创建记录时，您没有结束日期"，因此必须使用现在"计算出来)，那么我会考虑每晚安排的工作去重新计算所有这些特定记录，以便它们每天更新 - 然后当输入结束日期"时，该记录不会包含在此批量更新中.

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.

这篇关于计算两个日期之间有多少个工作日 - T-SQL?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！