在两个日期之间获取工作日数量的T-SQL

Question

在两个日期之间获取工作日数量的T-SQL

4

我希望计算两个给定日期之间的工作日数量。例如，如果我想计算2013-01-10和2013-01-15之间的工作日，则结果必须是3个工作日（我不考虑该时间段的最后一天，并且减去星期六和星期日）。我有以下代码，适用于大多数情况，但不适用于我的示例。

  SELECT (DATEDIFF(day, '2013-01-10', '2013-01-15')) 
    - (CASE WHEN DATENAME(weekday, '2013-01-10') = 'Sunday' THEN 1 ELSE 0 END)
    - (CASE WHEN DATENAME(weekday, DATEADD(day, -1, '2013-01-15')) = 'Saturday' THEN 1 ELSE 0 END)

我该如何完成这个任务呢？我需要逐一检查每一天吗？还是有更简单的方法呢？

- Rocshy

1

你将如何考虑非周末的假期？ - Bimalesh Jha

可能是计算两个日期之间的工作日数的重复问题。 - mmmmmm

6个回答

2

对于这样的事情，我倾向于维护一个日历表，其中还包括银行节假日等。

我使用的脚本如下（请注意，我没有编写它，但忘记了我在哪里找到它）

SET DATEFIRST 1
SET NOCOUNT ON
GO

--Create ISO week Function (thanks BOL)
CREATE FUNCTION ISOweek ( @DATE DATETIME )
RETURNS INT
AS 
    BEGIN
        DECLARE @ISOweek INT
        SET @ISOweek = DATEPART(wk, @DATE) + 1 - DATEPART(wk, CAST(DATEPART(yy, @DATE) AS CHAR(4)) + '0104')
        --Special cases: Jan 1-3 may belong to the previous year
        IF ( @ISOweek = 0 ) 
            SET @ISOweek = dbo.ISOweek(CAST(DATEPART(yy, @DATE) - 1 AS CHAR(4)) + '12' + CAST(24 + DATEPART(DAY, @DATE) AS CHAR(2))) + 1
        --Special case: Dec 29-31 may belong to the next year
        IF ( ( DATEPART(mm, @DATE) = 12 )
             AND ( ( DATEPART(dd, @DATE) - DATEPART(dw, @DATE) ) >= 28 )
           ) 
            SET @ISOweek = 1
        RETURN(@ISOweek)
    END
GO
--END ISOweek

--CREATE Easter algorithm function 
--Thanks to Rockmoose (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=45689)
CREATE FUNCTION fnDLA_GetEasterdate ( @year INT )
RETURNS CHAR(8)
AS 
    BEGIN
    -- Easter date algorithm of Delambre
        DECLARE @A INT ,
            @B INT ,
            @C INT ,
            @D INT ,
            @E INT ,
            @F INT ,
            @G INT ,
            @H INT ,
            @I INT ,
            @K INT ,
            @L INT ,
            @M INT ,
            @O INT ,
            @R INT              

        SET @A = @YEAR % 19
        SET @B = @YEAR / 100
        SET @C = @YEAR % 100
        SET @D = @B / 4
        SET @E = @B % 4
        SET @F = ( @B + 8 ) / 25
        SET @G = ( @B - @F + 1 ) / 3
        SET @H = ( 19 * @A + @B - @D - @G + 15 ) % 30
        SET @I = @C / 4
        SET @K = @C % 4
        SET @L = ( 32 + 2 * @E + 2 * @I - @H - @K ) % 7
        SET @M = ( @A + 11 * @H + 22 * @L ) / 451
        SET @O = 22 + @H + @L - 7 * @M

        IF @O > 31 
            BEGIN
                SET @R = @O - 31 + 400 + @YEAR * 10000
            END
        ELSE 
            BEGIN
                SET @R = @O + 300 + @YEAR * 10000
            END 

        RETURN @R
    END
GO
--END fnDLA_GetEasterdate

--Create the table
CREATE TABLE MyDateTable
    (
      FullDate DATETIME NOT NULL
                        CONSTRAINT PK_FullDate PRIMARY KEY CLUSTERED ,
      Period INT ,
      ISOWeek INT ,
      WorkingDay VARCHAR(1) CONSTRAINT DF_MyDateTable_WorkDay DEFAULT 'Y'
    )
GO
--End table create

--Populate table with required dates
DECLARE @DateFrom DATETIME ,
    @DateTo DATETIME ,
    @Period INT
SET @DateFrom = CONVERT(DATETIME, '20000101')
 --yyyymmdd (1st Jan 2000) amend as required
SET @DateTo = CONVERT(DATETIME, '20991231')
 --yyyymmdd (31st Dec 2099) amend as required
WHILE @DateFrom <= @DateTo 
    BEGIN
        SET @Period = CONVERT(INT, LEFT(CONVERT(VARCHAR(10), @DateFrom, 112), 6))
        INSERT  MyDateTable
                ( FullDate ,
                  Period ,
                  ISOWeek
                )
                SELECT  @DateFrom ,
                        @Period ,
                        dbo.ISOweek(@DateFrom)
        SET @DateFrom = DATEADD(dd, +1, @DateFrom)
    END
GO
--End population


/* Start of WorkingDays UPDATE */
UPDATE  MyDateTable
SET     WorkingDay = 'B' --B = Bank Holiday
--------------------------------EASTER---------------------------------------------
WHERE   FullDate = DATEADD(dd, -2, CONVERT(DATETIME, dbo.fnDLA_GetEasterdate(DATEPART(yy, FullDate)))) --Good Friday
        OR FullDate = DATEADD(dd, +1, CONVERT(DATETIME, dbo.fnDLA_GetEasterdate(DATEPART(yy, FullDate))))
 --Easter Monday
GO

UPDATE  MyDateTable
SET     WorkingDay = 'B'
--------------------------------NEW YEAR-------------------------------------------
WHERE   FullDate IN ( SELECT    MIN(FullDate)
                      FROM      MyDateTable
                      WHERE     DATEPART(mm, FullDate) = 1
                                AND DATEPART(dw, FullDate) NOT IN ( 6, 7 )
                      GROUP BY  DATEPART(yy, FullDate) )
---------------------MAY BANK HOLIDAYS(Always Monday)------------------------------
        OR FullDate IN ( SELECT MIN(FullDate)
                         FROM   MyDateTable
                         WHERE  DATEPART(mm, FullDate) = 5
                                AND DATEPART(dw, FullDate) = 1
                         GROUP BY DATEPART(yy, FullDate) )
        OR FullDate IN ( SELECT MAX(FullDate)
                         FROM   MyDateTable
                         WHERE  DATEPART(mm, FullDate) = 5
                                AND DATEPART(dw, FullDate) = 1
                         GROUP BY DATEPART(yy, FullDate) )
--------------------AUGUST BANK HOLIDAY(Always Monday)------------------------------
        OR FullDate IN ( SELECT MAX(FullDate)
                         FROM   MyDateTable
                         WHERE  DATEPART(mm, FullDate) = 8
                                AND DATEPART(dw, FullDate) = 1
                         GROUP BY DATEPART(yy, FullDate) )
--------------------XMAS(Move to next working day if on Sat/Sun)--------------------
        OR FullDate IN ( SELECT CASE WHEN DATEPART(dw, FullDate) IN ( 6, 7 ) THEN DATEADD(dd, +2, FullDate)
                                     ELSE FullDate
                                END
                         FROM   MyDateTable
                         WHERE  DATEPART(mm, FullDate) = 12
                                AND DATEPART(dd, FullDate) IN ( 25, 26 ) )
GO

---------------------------------------WEEKENDS--------------------------------------
UPDATE  MyDateTable
SET     WorkingDay = 'N'
WHERE   DATEPART(dw, FullDate) IN ( 6, 7 )
GO
/* End of WorkingDays UPDATE */

--SELECT * FROM MyDateTable ORDER BY 1
DROP FUNCTION fnDLA_GetEasterdate
DROP FUNCTION ISOweek
--DROP TABLE MyDateTable

SET NOCOUNT OFF

一旦您创建了表格，找到工作日的数量就非常容易：

SELECT  COUNT(FullDate) AS WorkingDays
FROM    dbo.tbl_WorkingDays
WHERE   WorkingDay = 'Y'
        AND FullDate >= CONVERT(DATETIME, '10/01/2013', 103)
        AND FullDate <  CONVERT(DATETIME, '15/01/2013', 103)

请注意，此脚本包含英国银行假期，我不确定您所在的地区。

- HeavenCore

关于你的第一个函数，SQL Server 2008及更高版本在DATEPART中使用ISO_WEEK作为参数，这比UDF更有效率。 - GarethD

我同意，但是你知道，这只运行一次，一旦日历表被建立，该函数就被丢弃并再也不会使用了，引用克拉克·盖博的话，“坦白地说，亲爱的，我才不在乎呢”哈哈 - HeavenCore

2

你的查询在结尾使用了BETWEEN，这将返回4行结果。OP声明他们需要丢弃范围内的最后一天，因此查询应该使用>=和<。另外，为什么不使用标准日期格式（如YYYYMMDD），这样就不必进行所有转换操作了？https://sqlblog.org/blogs/aaron_bertrand/archive/2009/10/16/bad-habits-to-kick-mishandling-date-range-queries.aspx https://sqlblog.org/blogs/aaron_bertrand/archive/2011/10/19/what-do-between-and-the-devil-have-in-common.aspx - Aaron Bertrand

如果您浏览我提出的问题，您会发现一个更好的iso_week函数。 - t-clausen.dk

1

这是一个简单的函数，用于计算工作日，不包括星期六和星期天（不需要计算假日）：

CREATE FUNCTION dbo.udf_GetBusinessDays (

@START_DATE DATE,
@END_DATE DATE

)
RETURNS INT
WITH EXECUTE AS CALLER
AS

BEGIN

 DECLARE @NUMBER_OF_DAYS INT = 0;
 DECLARE @DAY_COUNTER INT = 0;
 DECLARE @BUSINESS_DAYS INT = 0;
 DECLARE @CURRENT_DATE DATE;
 DECLARE @DAYNAME NVARCHAR(9)

 SET @NUMBER_OF_DAYS = DATEDIFF(DAY, @START_DATE, @END_DATE);

 WHILE @DAY_COUNTER <= @NUMBER_OF_DAYS
 BEGIN

    SET @CURRENT_DATE = DATEADD(DAY, @DAY_COUNTER, @START_DATE)
    SET @DAYNAME = DATENAME(WEEKDAY, @CURRENT_DATE)
    SET @DAY_COUNTER += 1

    IF @DAYNAME = N'Saturday' OR @DAYNAME = N'Sunday'
    BEGIN
        CONTINUE
    END
    ELSE
    BEGIN
        SET @BUSINESS_DAYS += 1
    END
 END

 RETURN @BUSINESS_DAYS
END
GO

- SHennessy

0

通过结合@Aaron Bertrand的答案和@HeavenCore的复活节计算，并添加了一些我自己的代码，这段代码创建了一个从2000年到2049年的日历，其中包括英国（英格兰）银行假期。使用方法和注意事项与Aaron的答案相同：

DECLARE @s DATE, @e DATE;
SELECT @s = '2000-01-01' , @e = '2049-12-31';

    -- Insert statements for procedure here
CREATE TABLE dbo.Calendar
(
  dt DATE PRIMARY KEY, -- use SMALLDATETIME if < SQL Server 2008
  IsWorkDay BIT
);

INSERT dbo.Calendar(dt, IsWorkDay)
  SELECT DATEADD(DAY, n-1, '2000-01-01'), 1 
  FROM
  (
    SELECT TOP (DATEDIFF(DAY, @s, @e)+1) ROW_NUMBER() 
      OVER (ORDER BY s1.[object_id])
      FROM sys.all_objects AS s1
      CROSS JOIN sys.all_objects AS s2
  ) AS x(n);

SET DATEFIRST 1;

-- weekends
UPDATE dbo.Calendar SET IsWorkDay = 0 
  WHERE DATEPART(WEEKDAY, dt) IN (6,7);

-- Christmas
UPDATE dbo.Calendar SET IsWorkDay = 0 
  WHERE IsWorkDay = 1 and MONTH(dt) = 12 and 
  (DAY(dt) in (25,26) or
  (DAY(dt) in (27, 28) and DATEPART(WEEKDAY, dt) IN (1,2)) );
  

-- New Year
UPDATE dbo.Calendar SET IsWorkDay = 0 
  WHERE IsWorkDay = 1 and MONTH(dt) = 1 AND 
  ( DAY(dt) = 1 or (DAY(dt) IN (2,3) AND DATEPART(WEEKDAY, dt)=1 ));


-- Easter
UPDATE dbo.Calendar SET IsWorkDay = 0 
  WHERE   dt = DATEADD(dd, -2, CONVERT(DATETIME, dbo.fnDLA_GetEasterdate(DATEPART(yy, dt)))) --Good Friday
        OR dt = DATEADD(dd, +1, CONVERT(DATETIME, dbo.fnDLA_GetEasterdate(DATEPART(yy, dt))))  --Easter Monday

-- May Day (first Monday in May)
UPDATE dbo.Calendar SET IsWorkDay = 0 
  WHERE MONTH(dt) = 5 AND DATEPART(WEEKDAY, dt)=1 and DAY(DT)<8;

-- Spring Bank Holiday (last Monday in May apart from 2022 when moved to include Platinum Jubilee bank holiday)
UPDATE dbo.Calendar SET IsWorkDay = 0 
  WHERE 
  (YEAR(dt)=2022 and MONTH(dt) = 6 AND DAY(dt) IN (2,3)) OR 
  (YEAR(dt)<>2022 and MONTH(dt) = 5 AND DATEPART(WEEKDAY, dt)=1 and DAY(DT)>24);

-- Summer Bank Holiday (last Monday in August)
UPDATE dbo.Calendar SET IsWorkDay = 0 
  WHERE MONTH(dt) = 8 AND DATEPART(WEEKDAY, dt)=1 and DAY(DT)>24;

- Jon Roberts

0

这是我通常使用的方法（当不使用日历表时）：

DECLARE @T TABLE (Date1 DATE, Date2 DATE);
INSERT @T VALUES ('20130110', '20130115'), ('20120101', '20130101'), ('20120611', '20120701');

SELECT  Date1, Date2, WorkingDays
FROM    @T t
        CROSS APPLY
        (   SELECT  [WorkingDays] = COUNT(*)
            FROM    Master..spt_values s
            WHERE   s.Number BETWEEN 1 AND DATEDIFF(DAY, t.date1, t.Date2)
            AND     s.[Type] = 'P'
            AND     DATENAME(WEEKDAY, DATEADD(DAY, s.number, t.Date1)) NOT IN ('Saturday', 'Sunday')
        ) wd

如果你和我一样有一个包含假期的表格，你也可以将其添加进来：

SELECT  Date1, Date2, WorkingDays
FROM    @T t
        CROSS APPLY
        (   SELECT  [WorkingDays] = COUNT(*)
            FROM    Master..spt_values s
            WHERE   s.Number BETWEEN 1 AND DATEDIFF(DAY, t.date1, t.Date2)
            AND     s.[Type] = 'P'
            AND     DATENAME(WEEKDAY, DATEADD(DAY, s.number, t.Date1)) NOT IN ('Saturday', 'Sunday')
            AND     NOT EXISTS
                    (   SELECT  1
                        FROM    HolidayTable ht
                        WHERE   ht.Date = DATEADD(DAY, s.number, t.Date1)
                    )
        ) wd

如果您的日期相差不超过2047天，上述方法才有效。如果您需要计算更大范围的日期，可以使用以下方法：

SELECT  Date1, Date2, WorkingDays
FROM    @T t
        CROSS APPLY
        (   SELECT  [WorkingDays] = COUNT(*)
            FROM    (   SELECT  [Number] = ROW_NUMBER() OVER(ORDER BY s.number)
                        FROM    Master..spt_values s
                                CROSS JOIN Master..spt_values s2
                    ) s
            WHERE   s.Number BETWEEN 1 AND DATEDIFF(DAY, t.date1, t.Date2)
            AND     DATENAME(WEEKDAY, DATEADD(DAY, s.number, t.Date1)) NOT IN ('Saturday', 'Sunday')
        ) wd

- GarethD

0

我用 SQL SERVER 2008 (MS SQL) 写的代码，对我来说很好用。希望能对您有所帮助。

     DECLARE  @COUNTS int,                       
     @STARTDATE  date,
     @ENDDATE date
      SET @STARTDATE ='01/21/2013' /*Start date in mm/dd/yyy */
      SET @ENDDATE ='01/26/2013' /*End date in mm/dd/yyy */
     SET @COUNTS=0
      WHILE (@STARTDATE<=@ENDDATE)

       BEGIN
    /*Check for holidays*/
   IF  ( DATENAME(weekday,@STARTDATE)<>'Saturday' and  DATENAME(weekday,@STARTDATE)<>'Sunday')                                

    BEGIN 

    SET @COUNTS=@COUNTS+1
    END
    SET @STARTDATE=DATEADD(day,1,@STARTDATE)
    END
    /* Display the no of working days */
    SELECT @COUNTS

- KESAVAN PURUSOTHAMAN

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Aaron Bertrand · Accepted Answer

请使用日历表。 SQL Server 不知道国家假期、公司活动、自然灾害等信息。建立一个日历表相当容易，占用的空间极小，并且如果被引用足够多次，则会在内存中。

以下是一个示例，创建了一个包含30年日期（2000年至2029年）的日历表，但只需占用磁盘上的200 KB（如果使用分页压缩则为136 KB）。这几乎肯定比运行时处理某些公共表表达式（CTE）或其他集合所需的内存授权要少。

CREATE TABLE dbo.Calendar
(
  dt DATE PRIMARY KEY, -- use SMALLDATETIME if < SQL Server 2008
  IsWorkDay BIT
);

DECLARE @s DATE, @e DATE;
SELECT @s = '2000-01-01' , @e = '2029-12-31';

INSERT dbo.Calendar(dt, IsWorkDay)
  SELECT DATEADD(DAY, n-1, '2000-01-01'), 1 
  FROM
  (
    SELECT TOP (DATEDIFF(DAY, @s, @e)+1) ROW_NUMBER() 
      OVER (ORDER BY s1.[object_id])
      FROM sys.all_objects AS s1
      CROSS JOIN sys.all_objects AS s2
  ) AS x(n);

SET DATEFIRST 1;

-- weekends
UPDATE dbo.Calendar SET IsWorkDay = 0 
  WHERE DATEPART(WEEKDAY, dt) IN (6,7);

-- Christmas
UPDATE dbo.Calendar SET IsWorkDay = 0 
  WHERE MONTH(dt) = 12
  AND DAY(dt) = 25
  AND IsWorkDay = 1;

-- continue with other holidays, known company events, etc.

现在你想要的查询非常容易编写:

SELECT COUNT(*) FROM dbo.Calendar
  WHERE dt >= '20130110'
    AND dt <  '20130115'
    AND IsWorkDay = 1;

有关日历表的更多信息：

http://web.archive.org/web/20070611150639/http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

有关生成不带循环的集合的更多信息：

http://www.sqlperformance.com/tag/date-ranges

此外，要小心像依赖DATENAME的英文输出这样的小细节。我见过一些应用程序崩溃, 因为某些用户有不同的语言设置，如果您依赖于WEEKDAY，请确保适当地设置DATEFIRST设置…