T-SQL get number of working days between 2 dates

问题:

I want to calculate the number of working days between 2 given dates. For example if I want to calculate the working days between 2013-01-10 and 2013-01-15, the result must be 3 working days (I don't take into consideration the last day in that interval and I subtract the Saturdays and Sundays). I have the following code that works for most of the cases, except the one in my example.

  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)

How can I accomplish this? Do I have to go through all the days and check them? Or is there an easy way to do this.

回答1:

Please, please, please use a calendar table. SQL Server doesn't know anything about national holidays, company events, natural disasters, etc. A calendar table is fairly easy to build, takes an extremely small amount of space, and will be in memory if it is referenced enough.

Here is an example that creates a calendar table with 30 years of dates (2000 -> 2029) but requires only 200 KB on disk (136 KB if you use page compression). That is almost guaranteed to be less than the memory grant required to process some CTE or other set at runtime.

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.

Now the query you're after is quite simple to write:

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

More info on calendar tables:

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

More info on generating sets without loops:

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

Also beware of little things like relying on the English output of DATENAME. I've seen several applications break because some users had a different language setting, and if you're relying on WEEKDAY be sure you set your DATEFIRST setting appropriately...

回答2:

For stuff like this i tend to maintain a calendar table that also includes bank holidays etc.

The script i use for this is as follows (Note that i didnt write it @ i forget where i found it)

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

Once you have created the table, finding the number of working days is easy peasy:

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)

Note that this script includes UK bank holidays, i'm not sure what region you're in.

回答3:

Here's a simple function that counts working days not including Saturday and Sunday (when counting holidays isn't necessary):

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

回答4:

This is the method I normally use (When not using a calendar table):

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

If like I do you have a table with holidays in you can add this in too:

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

The above will only work if your dates are within 2047 days of each other, if you are likely to be calculating larger date ranges you can use this:

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

回答5:

I did my code in SQL SERVER 2008 (MS SQL) . It works fine for me. I hope it will help you.

     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