Policz dni robocze między dwoma datami

W obliczanie dni roboczych możesz znaleźć dobry artykuł na ten temat, ale jak widać nie jest on aż tak zaawansowany.

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

Jeśli chcesz użyć niestandardowego kalendarza, być może będziesz musiał dodać kilka sprawdzeń i parametrów. Mam nadzieję, że będzie to dobry punkt wyjścia.

Wszystkie podziękowania należą się Bogdanowi Maximowi i Peterowi Mortensenowi. To jest ich post, właśnie dodałem wakacje do funkcji (zakłada to, że masz tabelę "tblHolidays" z polem datetime "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)
*/

Moja wersja zaakceptowanej odpowiedzi jako funkcja za pomocą DATEPART, więc nie muszę robić porównywania łańcuchów w linii z

DATENAME(dw, @StartDate) = 'Sunday'

Anyway, here ' s My business datediff function

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

Innym podejściem do obliczania dni roboczych jest użycie pętli WHILE, która w zasadzie iteruje przez zakres dat i zwiększa go o 1, Gdy dni znajdują się w przedziale od poniedziałku do piątku. Kompletny skrypt do obliczania dni roboczych za pomocą pętli WHILE jest pokazany poniżej:

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

Chociaż opcja pętli WHILE jest czystsza i używa mniej linii kodu, może być wąskim gardłem wydajności w Twoim środowisku, szczególnie gdy zakres dat obejmuje kilka lat.

Więcej metod obliczania dni i godzin pracy znajdziesz w tym artykule: https://www.sqlshack.com/how-to-calculate-work-days-and-hours-in-sql-server/

 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

(brakuje mi kilku punktów do komentowania)

Jeśli zdecydujesz się zrezygnować z dnia +1 w eleganckim rozwiązaniu CMS , pamiętaj, że jeśli Data rozpoczęcia i zakończenia są w ten sam weekend, otrzymasz odpowiedź negatywną. Ie., Od 2008/10/26 do 2008/10/26 zwraca -1.

Moje dość uproszczone rozwiązanie:

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

.. który również ustawia wszystkie błędne posty z datą początkową po datą końcową na zero. Coś, czego możesz lub nie szukasz.

Dla różnicy między datami włącznie z wakacjami poszedłem w ten sposób:

1) Tabela ze świętami:

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

2) miałem taką tabelę plannings i chciałem wypełnić kolumnę Work_Days która była pusta:

    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) więc aby "Work_Days" później wypełnić moją kolumnę po prostu musiał:

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

Cheers

Oto wersja, która działa dobrze (myślę). Holiday table zawiera kolumny Holiday_date, które zawierają święta, które obserwuje Twoja firma.

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 

Wiem, że to stare pytanie, ale potrzebowałem formuły na dni robocze z wyłączeniem daty rozpoczęcia, Ponieważ mam kilka pozycji i potrzebuję dni, aby prawidłowo się zgromadzić.

Żadna z nie-iteracyjnych odpowiedzi nie zadziałała dla mnie.

Użyłem definicji typu

Ilość razy mija północ do poniedziałku, wtorku, środy, czwartku i piątku

(inni mogą liczyć północ do soboty zamiast poniedziałku)

Skończyłem z tym wzór

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 */

Jest to w zasadzie odpowiedź CMS bez polegania na określonym ustawieniu języka. A ponieważ robimy zdjęcia dla generic, oznacza to, że powinno działać również dla wszystkich ustawień {2]}.

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, ...) zawsze używa granicy od soboty do niedzieli przez tygodnie, więc wyrażenie jest deterministyczne i nie musi być modyfikowane (o ile nasza definicja dni tygodnia jest konsekwentnie od poniedziałku do piątku.) Numeracja dni różni się w zależności od ustawienia @@datefirst i zmodyfikowanych obliczeń poradź sobie z tą korektą z niewielkimi komplikacjami jakiejś arytmetyki modularnej.

Czystszym sposobem radzenia sobie z sobotą/niedzielą jest przetłumaczenie dat przed wyodrębnieniem wartości dnia tygodnia. Po przesunięciu wartości będą z powrotem zgodne ze stałą (i prawdopodobnie bardziej znaną) numeracją, która zaczyna się od 1 w niedzielę, a kończy się na 7 w sobotę.

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

Prześledziłem tę formę rozwiązania przynajmniej do 2002 roku i artykułu Itzika Ben-Gana. ( https://technet.microsoft.com/en-us/library/aa175781 (V = SQL.80).aspx) chociaż wymagało to niewielkiego dostosowania, ponieważ nowsze typy date nie pozwalają na arytmetykę dat, poza tym jest identyczne.

Edytuj: Dodałem +1, które w jakiś sposób zostały przerwane. Warto również zauważyć, że metoda ta zawsze liczy początek i koniec dni. Zakłada się również, że Data zakończenia jest w dniu lub po dacie rozpoczęcia.

Użycie tabeli daty:

    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;

Jeśli tego nie masz, możesz użyć tabeli liczb:

    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

Oba powinny być szybkie i to usuwa wieloznaczność / złożoność. Pierwsza opcja jest najlepsza, ale jeśli nie masz tabeli kalendarza, możesz zawsze utworzyć tabelę liczb z CTE.

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

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

Wziąłem różne przykłady tutaj, ale w mojej szczególnej sytuacji mamy @ PromisedDate dla dostawy i @ ReceivedDate dla faktycznego otrzymania przedmiotu. Gdy element został odebrany przed "PromisedDate" obliczenia nie były sumowane poprawnie, chyba że zamówiłem daty przekazane do funkcji według kolejności kalendarza. Nie chcąc za każdym razem sprawdzać dat, zmieniłem funkcję, aby obsłużyć to za mnie.

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

Jeśli chcesz dodać dni robocze do danej daty, Możesz utworzyć funkcję, która zależy od tabeli kalendarza, opisanej poniżej:

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

Podobnie jak w przypadku DATEDIFF, nie uważam daty końcowej za część interwału. Liczba (na przykład) niedziel między @StartDate i @EndDate jest liczbą niedziel między" początkowym "poniedziałkiem a @EndDate minus liczba niedziel między tym" początkowym " poniedziałkiem a @StartDate. Wiedząc o tym, możemy obliczyć liczbę dni roboczych w następujący sposób:

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

To dla mnie działa, w moim kraju w sobotę i niedzielę są dni wolne od pracy.

Dla mnie ważny jest czas @StartDate i @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

Tworzenie funkcji typu:

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

Możesz wywołać funkcję w następujący sposób:

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

Lub jak:

select dbo.fn_WorkDays(StartDate, EndDate) 
from table1

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

End

Znalazłem poniżej TSQL dość eleganckie rozwiązanie (nie mam uprawnień do uruchamiania funkcji). Znalazłem DATEDIFF ignoruje DATEFIRST i chciałem, żeby mój pierwszy dzień tygodnia był poniedziałkiem. Chciałem również, aby pierwszy dzień roboczy był ustawiony na zero, a jeśli wypadnie w weekend, poniedziałek będzie zerem. To może pomóc komuś, kto ma nieco inne wymagania:)

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*/ 

Jednym z podejść jest "chodzenie daty" Od początku do końca w połączeniu z wyrażeniem przypadku, które sprawdza, czy dzień nie jest sobota lub niedziela i oznaczanie go (1 dla dnia tygodnia, 0 dla weekendu). A na koniec wystarczy zsumować flagi (byłaby równa liczbie 1-FLAG, ponieważ druga flaga jest równa 0), aby dać liczbę dni tygodnia.

Możesz użyć funkcji Narzędzia typu GetNums (numer startowy,numer końcowy), która generuje serię liczb dla "zapętlenia" od daty rozpoczęcia Do daty zakończenia. Refer http://tsql.solidq.com/SourceCodes/GetNums.txt do realizacji. Logika może być również rozszerzona, aby zaspokoić święta (powiedzmy, jeśli masz tabelę świąt)

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)

Pożyczyłem kilka pomysłów od innych, aby stworzyć moje rozwiązanie. Używam kodu inline, aby ignorować weekendy i święta federalne. W moim środowisku EndDate może być null, ale nigdy nie poprzedzi 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

Żadna z powyższych funkcji nie pracuje w tym samym tygodniu ani nie zajmuje się wakacjami. Napisałem to:

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