Считайте рабочие дни между двумя датами

В Расчет рабочих дней вы можете найти хорошую статью на эту тему, но, как видите, она не такая уж сложная.

--Changing current database to the Master database allows function to be shared by everyone.
USE MASTER
GO
--If the function already exists, drop it.
IF EXISTS
(
    SELECT *
    FROM dbo.SYSOBJECTS
    WHERE ID = OBJECT_ID(N'[dbo].[fn_WorkDays]')
    AND XType IN (N'FN', N'IF', N'TF')
)
DROP FUNCTION [dbo].[fn_WorkDays]
GO
 CREATE FUNCTION dbo.fn_WorkDays
--Presets
--Define the input parameters (OK if reversed by mistake).
(
    @StartDate DATETIME,
    @EndDate   DATETIME = NULL --@EndDate replaced by @StartDate when DEFAULTed
)

--Define the output data type.
RETURNS INT

AS
--Calculate the RETURN of the function.
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
GO

Если вам нужно использовать собственный календарь, вам может потребоваться добавить некоторые проверки и некоторые параметры. Надеюсь, это станет хорошей отправной точкой.

В рабочие дни, с понедельника по пятницу, вы можете сделать это с помощью одного оператора SELECT, например:

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2008/10/01'
SET @EndDate = '2008/10/31'


SELECT
   (DATEDIFF(dd, @StartDate, @EndDate) + 1)
  -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
  -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)

Если вы хотите включить праздники, вам нужно немного поработать ...

 DECLARE @TotalDays INT,@WorkDays INT
 DECLARE @ReducedDayswithEndDate INT
 DECLARE @WeekPart INT
 DECLARE @DatePart INT

 SET @TotalDays= DATEDIFF(day, @StartDate, @EndDate) +1
 SELECT @ReducedDayswithEndDate = CASE DATENAME(weekday, @EndDate)
  WHEN 'Saturday' THEN 1
  WHEN 'Sunday' THEN 2
  ELSE 0 END 
 SET @TotalDays=@TotalDays-@ReducedDayswithEndDate
 SET @WeekPart=@TotalDays/7;
 SET @DatePart=@TotalDays%7;
 SET @WorkDays=(@WeekPart*5)+@DatePart

 RETURN @WorkDays

DECLARE @StartDate datetime,@EndDate datetime

select @StartDate='3/2/2010', @EndDate='3/7/2010'

DECLARE @TotalDays INT,@WorkDays INT

DECLARE @ReducedDayswithEndDate INT

DECLARE @WeekPart INT

DECLARE @DatePart INT

SET @TotalDays= DATEDIFF(day, @StartDate, @EndDate) +1

SELECT @ReducedDayswithEndDate = CASE DATENAME(weekday, @EndDate)
    WHEN 'Saturday' THEN 1
    WHEN 'Sunday' THEN 2
    ELSE 0 END

SET @TotalDays=@TotalDays-@ReducedDayswithEndDate

SET @WeekPart=@TotalDays/7;

SET @DatePart=@TotalDays%7;

SET @WorkDays=(@WeekPart*5)+@DatePart

SELECT @WorkDays

Моя версия принятого ответа как функция с использованием DATEPART, поэтому мне не нужно проводить сравнение строк в строке с

DATENAME(dw, @StartDate) = 'Sunday'

Во всяком случае, вот моя бизнес-функция dateiff

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION BDATEDIFF
(
    @startdate as DATETIME,
    @enddate as DATETIME
)
RETURNS INT
AS
BEGIN
    DECLARE @res int

SET @res = (DATEDIFF(dd, @startdate, @enddate) + 1)
    -(DATEDIFF(wk, @startdate, @enddate) * 2)
    -(CASE WHEN DATEPART(dw, @startdate) = 1 THEN 1 ELSE 0 END)
    -(CASE WHEN DATEPART(dw, @enddate) = 7 THEN 1 ELSE 0 END)

    RETURN @res
END
GO

CREATE FUNCTION x
(
    @StartDate DATETIME,
    @EndDate DATETIME
)
RETURNS INT
AS
BEGIN
    DECLARE @Teller INT

    SET @StartDate = DATEADD(dd,1,@StartDate)

    SET @Teller = 0
    IF DATEDIFF(dd,@StartDate,@EndDate) <= 0
    BEGIN
        SET @Teller = 0 
    END
    ELSE
    BEGIN
        WHILE
            DATEDIFF(dd,@StartDate,@EndDate) >= 0
        BEGIN
            IF DATEPART(dw,@StartDate) < 6
            BEGIN
                SET @Teller = @Teller + 1
            END
            SET @StartDate = DATEADD(dd,1,@StartDate)
        END
    END
    RETURN @Teller
END

(Я немного стесняюсь комментировать привилегии)

Если вы решите отказаться от +1 дня в Элегантное решение CMS, обратите внимание, что если ваша дата начала и дата окончания совпадают с одним и тем же уик-эндом, вы получите отрицательный ответ. Т.е. с 2008/10/26 по 2008/10/26 возвращает -1.

мое довольно упрощенное решение:

select @Result = (..CMS's answer..)
if  (@Result < 0)
        select @Result = 0
    RETURN @Result

.. который также устанавливает все ошибочные сообщения с Дата начала после Дата окончания в ноль. То, что вы, возможно, ищете, а можете и не ищете.

Для разницы между датами, включая праздники, я пошел следующим образом:

1) Таблица с праздниками:

    CREATE TABLE [dbo].[Holiday](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[Date] [datetime] NOT NULL)

2) У меня была такая таблица планов, и я хотел заполнить пустую колонку Work_Days:

    CREATE TABLE [dbo].[Plan_Phase](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Id_Plan] [int] NOT NULL,
[Id_Phase] [int] NOT NULL,
[Start_Date] [datetime] NULL,
[End_Date] [datetime] NULL,
[Work_Days] [int] NULL)

3) Итак, чтобы получить «Рабочие_дни» для последующего заполнения моей колонки, достаточно было:

SELECT Start_Date, End_Date,
 (DATEDIFF(dd, Start_Date, End_Date) + 1)
-(DATEDIFF(wk, Start_Date, End_Date) * 2)
-(SELECT COUNT(*) From Holiday Where Date  >= Start_Date AND Date <= End_Date)
-(CASE WHEN DATENAME(dw, Start_Date) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, End_Date) = 'Saturday' THEN 1 ELSE 0 END)
-(CASE WHEN (SELECT COUNT(*) From Holiday Where Start_Date  = Date) > 0 THEN 1 ELSE 0 END)
-(CASE WHEN (SELECT COUNT(*) From Holiday Where End_Date  = Date) > 0 THEN 1 ELSE 0 END) AS Work_Days
from Plan_Phase

Надеюсь, что смогу помочь.

Ваше здоровье

Я взял здесь различные примеры, но в моей конкретной ситуации у нас есть @PromisedDate для доставки и @ReceivedDate для фактического получения элемента. Когда элемент был получен до "PromisedDate", подсчет суммировался некорректно, если только я не упорядочил даты, передаваемые в функцию, в календарном порядке. Не желая каждый раз проверять даты, я изменил функцию, чтобы справиться с этим за меня.

Create FUNCTION [dbo].[fnGetBusinessDays]
(
 @PromiseDate date,
 @ReceivedDate date
)
RETURNS integer
AS
BEGIN
 DECLARE @days integer

 SELECT @days = 
    Case when @PromiseDate > @ReceivedDate Then
        DATEDIFF(d,@PromiseDate,@ReceivedDate) + 
        ABS(DATEDIFF(wk,@PromiseDate,@ReceivedDate)) * 2 +
        CASE 
            WHEN DATENAME(dw, @PromiseDate) <> 'Saturday' AND DATENAME(dw, @ReceivedDate) = 'Saturday' THEN 1 
            WHEN DATENAME(dw, @PromiseDate) = 'Saturday' AND DATENAME(dw, @ReceivedDate) <> 'Saturday' THEN -1 
            ELSE 0
        END +
        (Select COUNT(*) FROM CompanyHolidays 
            WHERE HolidayDate BETWEEN @ReceivedDate AND @PromiseDate 
            AND DATENAME(dw, HolidayDate) <> 'Saturday' AND DATENAME(dw, HolidayDate) <> 'Sunday')
    Else
        DATEDIFF(d,@PromiseDate,@ReceivedDate)  -
        ABS(DATEDIFF(wk,@PromiseDate,@ReceivedDate)) * 2  -
            CASE 
                WHEN DATENAME(dw, @PromiseDate) <> 'Saturday' AND DATENAME(dw, @ReceivedDate) = 'Saturday' THEN 1 
                WHEN DATENAME(dw, @PromiseDate) = 'Saturday' AND DATENAME(dw, @ReceivedDate) <> 'Saturday' THEN -1 
                ELSE 0
            END -
        (Select COUNT(*) FROM CompanyHolidays 
            WHERE HolidayDate BETWEEN @PromiseDate and @ReceivedDate 
            AND DATENAME(dw, HolidayDate) <> 'Saturday' AND DATENAME(dw, HolidayDate) <> 'Sunday')
    End


 RETURN (@days)

END

Вот версия, которая работает хорошо (я думаю). Таблица Holiday содержит столбцы Holiday_date, в которых указаны праздники, которые отмечает ваша компания.

DECLARE @RAWDAYS INT

   SELECT @RAWDAYS =  DATEDIFF(day, @StartDate, @EndDate )--+1
                    -( 2 * DATEDIFF( week, @StartDate, @EndDate ) )
                    + CASE WHEN DATENAME(dw, @StartDate) = 'Saturday' THEN 1 ELSE 0 END
                    - CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END 

   SELECT  @RAWDAYS - COUNT(*) 
     FROM HOLIDAY NumberOfBusinessDays
    WHERE [Holiday_Date] BETWEEN @StartDate+1 AND @EndDate 

Все кредиты Богдану Максиму и Петру Мортенсену. Это их сообщение, я только что добавил в функцию праздники (предполагается, что у вас есть таблица «tblHolidays» с полем даты и времени «HolDate».

--Changing current database to the Master database allows function to be shared by everyone.
USE MASTER
GO
--If the function already exists, drop it.
IF EXISTS
(
    SELECT *
    FROM dbo.SYSOBJECTS
    WHERE ID = OBJECT_ID(N'[dbo].[fn_WorkDays]')
    AND XType IN (N'FN', N'IF', N'TF')
)

DROP FUNCTION [dbo].[fn_WorkDays]
GO
 CREATE FUNCTION dbo.fn_WorkDays
--Presets
--Define the input parameters (OK if reversed by mistake).
(
    @StartDate DATETIME,
    @EndDate   DATETIME = NULL --@EndDate replaced by @StartDate when DEFAULTed
)

--Define the output data type.
RETURNS INT

AS
--Calculate the RETURN of the function.
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)
        --Subtract all holidays
        -(Select Count(*) from [DB04\DB04].[Gateway].[dbo].[tblHolidays]
          where  [HolDate] between @StartDate and @EndDate )
        )
    END  
GO
-- Test Script
/*
declare @EndDate datetime= dateadd(m,2,getdate())
print @EndDate
select  [Master].[dbo].[fn_WorkDays] (getdate(), @EndDate)
*/

Если вам нужно добавить рабочие дни к заданной дате, вы можете создать функцию, которая зависит от календарной таблицы, как описано ниже:

CREATE TABLE Calendar
(
  dt SMALLDATETIME PRIMARY KEY, 
  IsWorkDay BIT
);

--fill the rows with normal days, weekends and holidays.


create function AddWorkingDays (@initialDate smalldatetime, @numberOfDays int)
    returns smalldatetime as 

    begin
        declare @result smalldatetime
        set @result = 
        (
            select t.dt from
            (
                select dt, ROW_NUMBER() over (order by dt) as daysAhead from calendar 
                where dt > @initialDate
                and IsWorkDay = 1
                ) t
            where t.daysAhead = @numberOfDays
        )

        return @result
    end

Используя таблицу дат:

    DECLARE 
        @StartDate date = '2014-01-01',
        @EndDate date = '2014-01-31'; 
    SELECT 
        COUNT(*) As NumberOfWeekDays
    FROM dbo.Calendar
    WHERE CalendarDate BETWEEN @StartDate AND @EndDate
      AND IsWorkDay = 1;

Если у вас его нет, вы можете использовать таблицу чисел:

    DECLARE 
    @StartDate datetime = '2014-01-01',
    @EndDate datetime = '2014-01-31'; 
    SELECT 
    SUM(CASE WHEN DATEPART(dw, DATEADD(dd, Number-1, @StartDate)) BETWEEN 2 AND 6 THEN 1 ELSE 0 END) As NumberOfWeekDays
    FROM dbo.Numbers
    WHERE Number <= DATEDIFF(dd, @StartDate, @EndDate) + 1 -- Number table starts at 1, we want a 0 base

Оба они должны быть быстрыми и устранять двусмысленность / сложность. Первый вариант - лучший, но если у вас нет календарной таблицы, вы всегда можете создать таблицу чисел с CTE.

У меня это работает, в моей стране суббота и воскресенье - нерабочие дни.

Для меня важно время @StartDate и @EndDate.

CREATE FUNCTION [dbo].[fnGetCountWorkingBusinessDays]
(
    @StartDate as DATETIME,
    @EndDate as DATETIME
)
RETURNS INT
AS
BEGIN
    DECLARE @res int

SET @StartDate = CASE 
    WHEN DATENAME(dw, @StartDate) = 'Saturday' THEN DATEADD(dd, 2, DATEDIFF(dd, 0, @StartDate))
    WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN DATEADD(dd, 1, DATEDIFF(dd, 0, @StartDate))
    ELSE @StartDate END

SET @EndDate = CASE 
    WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN DATEADD(dd, 0, DATEDIFF(dd, 0, @EndDate))
    WHEN DATENAME(dw, @EndDate) = 'Sunday' THEN DATEADD(dd, -1, DATEDIFF(dd, 0, @EndDate))
    ELSE @EndDate END


SET @res =
    (DATEDIFF(hour, @StartDate, @EndDate) / 24)
  - (DATEDIFF(wk, @StartDate, @EndDate) * 2)

SET @res = CASE WHEN @res < 0 THEN 0 ELSE @res END

    RETURN @res
END

GO

Создайте такую ​​функцию, как:

CREATE FUNCTION dbo.fn_WorkDays(@StartDate DATETIME, @EndDate DATETIME= NULL )
RETURNS INT 
AS
BEGIN
       DECLARE @Days int
       SET @Days = 0

       IF @EndDate = NULL
              SET @EndDate = EOMONTH(@StartDate) --last date of the month

       WHILE DATEDIFF(dd,@StartDate,@EndDate) >= 0
       BEGIN
              IF DATENAME(dw, @StartDate) <> 'Saturday' 
                     and DATENAME(dw, @StartDate) <> 'Sunday' 
                     and Not ((Day(@StartDate) = 1 And Month(@StartDate) = 1)) --New Year's Day.
                     and Not ((Day(@StartDate) = 4 And Month(@StartDate) = 7)) --Independence Day.
              BEGIN
                     SET @Days = @Days + 1
              END

              SET @StartDate = DATEADD(dd,1,@StartDate)
       END

       RETURN  @Days
END

Вы можете вызвать функцию так:

select dbo.fn_WorkDays('1/1/2016', '9/25/2016')

Или вроде:

select dbo.fn_WorkDays(StartDate, EndDate) 
from table1

Это в основном ответ CMS без зависимости от конкретной языковой настройки. А поскольку мы снимаемся для универсального, это означает, что он должен работать и для всех настроек @@datefirst.

datediff(day, <start>, <end>) + 1 - datediff(week, <start>, <end>) * 2
    /* if start is a Sunday, adjust by -1 */
  + case when datepart(weekday, <start>) = 8 - @@datefirst then -1 else 0 end
    /* if end is a Saturday, adjust by -1 */
  + case when datepart(weekday, <end>) = (13 - @@datefirst) % 7 + 1 then -1 else 0 end

datediff(week, ...) всегда использует границу субботы и воскресенья в течение нескольких недель, поэтому это выражение является детерминированным и не нуждается в изменении (если наше определение дней недели постоянно с понедельника по пятницу). Нумерация дней зависит от настройки @@datefirst и модифицированные вычисления обрабатывают эту поправку с небольшим усложнением некоторой модульной арифметики.

Более чистый способ справиться с проблемой субботы / воскресенья - это перевести даты до извлечения значения дня недели. После сдвига значения вернутся в соответствие с фиксированной (и, вероятно, более привычной) нумерацией, которая начинается с 1 в воскресенье и заканчивается 7 в субботу.

datediff(day, <start>, <end>) + 1 - datediff(week, <start>, <end>) * 2
  + case when datepart(weekday, dateadd(day, @@datefirst, <start>)) = 1 then -1 else 0 end
  + case when datepart(weekday, dateadd(day, @@datefirst, <end>))   = 7 then -1 else 0 end

Я отслеживал эту форму решения, по крайней мере, до 2002 года и до статьи Ицика Бен-Гана. (https://technet.microsoft.com/en-us/library/aa175781(v=sql.80).aspx) Хотя это потребовало небольшой настройки, поскольку новые типы date не поддерживают арифметику даты, в остальном она идентична.

Обновлено: Я добавил обратно +1, который почему-то был отключен. Также стоит отметить, что этот метод всегда считает дни начала и окончания. Также предполагается, что дата окончания совпадает с датой начала или позже.

Create Function dbo.DateDiff_WeekDays 
(
@StartDate  DateTime,
@EndDate    DateTime
)
Returns Int
As

Begin   

Declare @Result Int = 0

While   @StartDate <= @EndDate
Begin 
    If DateName(DW, @StartDate) not in ('Saturday','Sunday')
        Begin
            Set @Result = @Result +1
        End
        Set @StartDate = DateAdd(Day, +1, @StartDate)
End

Return @Result

Конец

Другой подход к вычислению рабочих дней - использовать цикл WHILE, который в основном выполняет итерацию по диапазону дат и увеличивает его на 1 всякий раз, когда оказывается, что дни находятся в пределах с понедельника по пятницу. Полный скрипт для расчета рабочих дней с помощью цикла WHILE показан ниже:

CREATE FUNCTION [dbo].[fn_GetTotalWorkingDaysUsingLoop]
(@DateFrom DATE,
@DateTo   DATE
)
RETURNS INT
AS
     BEGIN
         DECLARE @TotWorkingDays INT= 0;
         WHILE @DateFrom <= @DateTo
             BEGIN
                 IF DATENAME(WEEKDAY, @DateFrom) IN('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday')
                     BEGIN
                         SET @TotWorkingDays = @TotWorkingDays + 1;
                 END;
                 SET @DateFrom = DATEADD(DAY, 1, @DateFrom);
             END;
         RETURN @TotWorkingDays;
     END;
GO

Хотя вариант цикла WHILE более понятен и использует меньше строк кода, он может стать узким местом производительности в вашей среде, особенно когда диапазон дат охватывает несколько лет.

Вы можете увидеть больше методов расчета рабочих дней и часов в этой статье: https://www.sqlshack.com/how-to-calculate-work-days-and-hours-in-sql-server/

Я нашел приведенный ниже TSQL довольно элегантным решением (у меня нет прав на запуск функций). Я обнаружил, что DATEDIFF игнорирует DATEFIRST, и я хотел, чтобы мой первый день недели был понедельником. Я также хотел, чтобы первый рабочий день был установлен на ноль, и если он выпадет на выходные, понедельник будет нулевым. Это может помочь тем, у кого немного другие требования :)

Не работает в праздничные дни

SET DATEFIRST 1
SELECT
,(DATEDIFF(DD,  [StartDate], [EndDate]))        
-(DATEDIFF(wk,  [StartDate], [EndDate]))        
-(DATEDIFF(wk, DATEADD(dd,-@@DATEFIRST,[StartDate]), DATEADD(dd,-@@DATEFIRST,[EndDate]))) AS [WorkingDays] 
FROM /*Your Table*/ 

Один из подходов состоит в том, чтобы «пройтись по датам» от начала до конца в сочетании с выражением case, которое проверяет, не является ли день субботой или воскресеньем, и отмечает его (1 для буднего дня, 0 для выходных). И в конце просто суммируйте флаги (это будет равно количеству 1-флагов, поскольку другой флаг равен 0), чтобы получить количество рабочих дней.

Вы можете использовать вспомогательную функцию типа GetNums (startNumber, endNumber), которая генерирует серию чисел для «цикла» от даты начала до даты окончания. Обратитесь к http://tsql.solidq.com/SourceCodes/GetNums.txt для реализации. Логика также может быть расширена для обслуживания праздников (скажем, если у вас есть праздничный стол).

declare @date1 as datetime = '19900101'
declare @date2 as datetime = '19900120'

select  sum(case when DATENAME(DW,currentDate) not in ('Saturday', 'Sunday') then 1 else 0 end) as noOfWorkDays
from dbo.GetNums(0,DATEDIFF(day,@date1, @date2)-1) as Num
cross apply (select DATEADD(day,n,@date1)) as Dates(currentDate)

Как и в случае с DATEDIFF, я не считаю дату окончания частью интервала. Количество (например) воскресений между @StartDate и @EndDate - это количество воскресений между «начальным» понедельником и @EndDate за вычетом количества воскресений между этим «начальным» понедельником и @StartDate. Зная это, мы можем рассчитать количество рабочих дней следующим образом:

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2018/01/01'
SET @EndDate = '2019/01/01'

SELECT DATEDIFF(Day, @StartDate, @EndDate) -- Total Days
  - (DATEDIFF(Day, 0, @EndDate)/7 - DATEDIFF(Day, 0, @StartDate)/7) -- Sundays
  - (DATEDIFF(Day, -1, @EndDate)/7 - DATEDIFF(Day, -1, @StartDate)/7) -- Saturdays

С уважением!

Я знаю, что это старый вопрос, но мне нужна была формула для рабочих дней, исключая дату начала, поскольку у меня есть несколько элементов, и мне нужно, чтобы дни правильно накапливались.

Ни один из неитеративных ответов у меня не сработал.

Я использовал такое определение, как

Number of times midnight to monday, tuesday, wednesday, thursday and friday is passed

(другие могут считать с полуночи до субботы вместо понедельника)

Я пришел к этой формуле

SELECT DATEDIFF(day, @StartDate, @EndDate) /* all midnights passed */
     - DATEDIFF(week, @StartDate, @EndDate) /* remove sunday midnights */
     - DATEDIFF(week, DATEADD(day, 1, @StartDate), DATEADD(day, 1, @EndDate)) /* remove saturday midnights */

Я позаимствовал некоторые идеи у других, чтобы создать свое решение. Я использую встроенный код, чтобы игнорировать выходные и федеральные праздники США. В моей среде EndDate может иметь значение null, но никогда не предшествует StartDate.

CREATE FUNCTION dbo.ufn_CalculateBusinessDays(
@StartDate DATE,
@EndDate DATE = NULL)

RETURNS INT
AS

BEGIN
DECLARE @TotalBusinessDays INT = 0;
DECLARE @TestDate DATE = @StartDate;


IF @EndDate IS NULL
    RETURN NULL;

WHILE @TestDate < @EndDate
BEGIN
    DECLARE @Month INT = DATEPART(MM, @TestDate);
    DECLARE @Day INT = DATEPART(DD, @TestDate);
    DECLARE @DayOfWeek INT = DATEPART(WEEKDAY, @TestDate) - 1; --Monday = 1, Tuesday = 2, etc.
    DECLARE @DayOccurrence INT = (@Day - 1) / 7 + 1; --Nth day of month (3rd Monday, for example)

    --Increment business day counter if not a weekend or holiday
    SELECT @TotalBusinessDays += (
        SELECT CASE
            --Saturday OR Sunday
            WHEN @DayOfWeek IN (6,7) THEN 0
            --New Year's Day
            WHEN @Month = 1 AND @Day = 1 THEN 0
            --MLK Jr. Day
            WHEN @Month = 1 AND @DayOfWeek = 1 AND @DayOccurrence = 3 THEN 0
            --G. Washington's Birthday
            WHEN @Month = 2 AND @DayOfWeek = 1 AND @DayOccurrence = 3 THEN 0
            --Memorial Day
            WHEN @Month = 5 AND @DayOfWeek = 1 AND @Day BETWEEN 25 AND 31 THEN 0
            --Independence Day
            WHEN @Month = 7 AND @Day = 4 THEN 0
            --Labor Day
            WHEN @Month = 9 AND @DayOfWeek = 1 AND @DayOccurrence = 1 THEN 0
            --Columbus Day
            WHEN @Month = 10 AND @DayOfWeek = 1 AND @DayOccurrence = 2 THEN 0
            --Veterans Day
            WHEN @Month = 11 AND @Day = 11 THEN 0
            --Thanksgiving
            WHEN @Month = 11 AND @DayOfWeek = 4 AND @DayOccurrence = 4 THEN 0
            --Christmas
            WHEN @Month = 12 AND @Day = 25 THEN 0
            ELSE 1
            END AS Result);

    SET @TestDate = DATEADD(dd, 1, @TestDate);
END

RETURN @TotalBusinessDays;
END

Ни одна из вышеперечисленных функций не работает на той же неделе и не связана с праздниками. Я написал это:

create FUNCTION [dbo].[ShiftHolidayToWorkday](@date date)
RETURNS date
AS
BEGIN
    IF DATENAME( dw, @Date ) = 'Saturday'
        SET @Date = DATEADD(day, - 1, @Date)

    ELSE IF DATENAME( dw, @Date ) = 'Sunday'
        SET @Date = DATEADD(day, 1, @Date)

    RETURN @date
END
GO

create FUNCTION [dbo].[GetHoliday](@date date)
RETURNS varchar(50)
AS
BEGIN
    declare @s varchar(50)

    SELECT @s = CASE
        WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year]  ) + '-01-01') = @date THEN 'New Year'
        WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year]+1) + '-01-01') = @date THEN 'New Year'
        WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year]  ) + '-07-04') = @date THEN 'Independence Day'
        WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year]  ) + '-12-25') = @date THEN 'Christmas Day'
        --WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year]) + '-12-31') = @date THEN 'New Years Eve'
        --WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year]) + '-11-11') = @date THEN 'Veteran''s Day'

        WHEN [Month] = 1  AND [DayOfMonth] BETWEEN 15 AND 21 AND [DayName] = 'Monday' THEN 'Martin Luther King Day'
        WHEN [Month] = 5  AND [DayOfMonth] >= 25             AND [DayName] = 'Monday' THEN 'Memorial Day'
        WHEN [Month] = 9  AND [DayOfMonth] <= 7              AND [DayName] = 'Monday' THEN 'Labor Day'
        WHEN [Month] = 11 AND [DayOfMonth] BETWEEN 22 AND 28 AND [DayName] = 'Thursday' THEN 'Thanksgiving Day'
        WHEN [Month] = 11 AND [DayOfMonth] BETWEEN 23 AND 29 AND [DayName] = 'Friday' THEN 'Day After Thanksgiving'
        ELSE NULL END
    FROM (
        SELECT
            [Year] = YEAR(@date),
            [Month] = MONTH(@date),
            [DayOfMonth] = DAY(@date),
            [DayName]   = DATENAME(weekday,@date)
    ) c

    RETURN @s
END
GO

create FUNCTION [dbo].GetHolidays(@year int)
RETURNS TABLE 
AS
RETURN (  
    select dt, dbo.GetHoliday(dt) as Holiday
    from (
        select dateadd(day, number, convert(varchar,@year) + '-01-01') dt
        from master..spt_values 
        where type='p' 
        ) d
    where year(dt) = @year and dbo.GetHoliday(dt) is not null
)

create proc UpdateHolidaysTable
as

if not exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'Holidays')
    create table Holidays(dt date primary key clustered, Holiday varchar(50))

declare @year int
set @year = 1990

while @year < year(GetDate()) + 20
begin
    insert into Holidays(dt, Holiday)
    select a.dt, a.Holiday
    from dbo.GetHolidays(@year) a
        left join Holidays b on b.dt = a.dt
    where b.dt is null

    set @year = @year + 1
end

create FUNCTION [dbo].[GetWorkDays](@StartDate DATE = NULL, @EndDate DATE = NULL)
RETURNS INT 
AS
BEGIN
    IF @StartDate IS NULL OR @EndDate IS NULL
        RETURN  0

    IF @StartDate >= @EndDate 
        RETURN  0

    DECLARE @Days int
    SET @Days = 0

    IF year(@StartDate) * 100 + datepart(week, @StartDate) = year(@EndDate) * 100 + datepart(week, @EndDate) 
        --same week
        select @Days = (DATEDIFF(dd, @StartDate, @EndDate))
      - (CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
      - (CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)
      - (select count(*) from Holidays where dt between @StartDate and @EndDate)
    ELSE
        --diff weeks
        select @Days = (DATEDIFF(dd, @StartDate, @EndDate) + 1)
      - (DATEDIFF(wk, @StartDate, @EndDate) * 2)
      - (CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
      - (CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)
      - (select count(*) from Holidays where dt between @StartDate and @EndDate)
 
    RETURN  @Days
END