Giorni lavorativi tra le date in una funzione di SQL Server con opzioni flessibili

Una funzione flessibile e riutilizzabile di SQL Server che calcolerà il numero di giorni tra due date con l'opzione di escludere festività o fine settimana

Se hai controllato gli altri articoli, potresti già avere la tabella, altrimenti puoi utilizzare il codice seguente per crearlo. Si basano su festività standard in Inghilterra e Galles. Nella pagina principale ci sono funzioni per altri Paesi.

CREATE TABLE Dates.Calendar(
CalendarDate DATETIME2 NOT NULL CONSTRAINT PK_CalendarDate PRIMARY KEY,
CalendarCA AS (DATEDIFF(DAY,DATEADD(DAY,1-DATEPART(DAY,CalendarDate),CalendarDate),CalendarDate)/7)+1 PERSISTED,
CalendarCD AS (DATEDIFF(DAY,CalendarDate,DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(DAY,1-DATEPART(DAY,CalendarDate),CalendarDate))))/7)+1 PERSISTED,
WeekDayID AS (DATEPART(weekday,[CalendarDate])),
WeekDayName AS (case DATEPART(weekday,[CalendarDate]) when (1) then 'Sunday' when (2) then 'Monday' when (3) then 'Tuesday' when (4) then 'Wednesday' when (5) then 'Thursday' when (6) then 'Friday' when (7) then 'Saturday'  end))
GO
DECLARE @D DATETIME2='1850-01-01'
WHILE @D<='2099-12-31' BEGIN
INSERT INTO Dates.Calendar(CalendarDate) SELECT @D
SET @D=DATEADD(DAY,1,@D)
END
GO
CREATE TABLE Dates.CalendarHolidays(CalendarDate DATETIME2 NOT NULL,CalendarFunction INT NOT NULL,HolidayType VARCHAR(100) NULL,CONSTRAINT PK_Holidays_Id PRIMARY KEY(CalendarDate,CalendarFunction))
GO
/*English & Welsh Holidays*/
INSERT INTO Dates.CalendarHolidays
SELECT CalendarDate,0,'New Years Day' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=1 AND DATEPART(DAY,CalendarDate)=1 UNION  --New Years Day
SELECT CalendarDate,0,'Good Friday' FROM Dates.Calendar WHERE CalendarDate=DATEADD(DAY,-2,Dates.GetEasterDate(DATEPART(YEAR,CalendarDate))) UNION --Good Friday
SELECT CalendarDate,0,'Easter Monday' FROM Dates.Calendar WHERE CalendarDate=DATEADD(DAY,1,Dates.GetEasterDate(DATEPART(YEAR,CalendarDate))) UNION --Easter Monday
SELECT CalendarDate,0,'May Holidays' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=5 AND WeekDayID=2 AND (CalendarCA=1 OR CalendarCD=1)UNION --May Holidays
SELECT CalendarDate,0,'August Holidays' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=8 AND WeekDayID=2 AND (CalendarCD=1) UNION --August Holidays
SELECT CalendarDate,0,'Christmas Day' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=12 AND DATEPART(DAY,CalendarDate)=25 UNION  --Christmas Day
SELECT CalendarDate,0,'Boxing Day' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=12 AND DATEPART(DAY,CalendarDate)=26  --Boxing Day
GO

Ora abbiamo i dati, possiamo creare una funzione che scorre ogni giorno dall'inizio alla fine e aggiunge 1 dipendente dalle variabili inserite come di seguito:

@DateFrom: la data di inizio del calcolo
@CalendarFunction - La funzione del tipo di festività che si desidera utilizzare
@DataTo: la fine dell'intervallo di date che si desidera utilizzare
@AdjustMode - Calcola i giorni tra le date come 0 Esclusivo o 1 Incluso del primo giorno.
@AdjustWeekend: esclude i fine settimana dai tuoi calcoli
@AdjustHolidays: esclude le festività se la funzione ferie corrisponde

CREATE FUNCTION Dates.GetDaysAdjusted(@DateFrom DATETIME2,@CalendarFunction INT,@DateTo AS DATETIME2,@AdjustMode BIT,@AdjustWeekEnds BIT,@AdjustHolidays BIT) RETURNS INT AS BEGIN
/*@AdjustMode 0=Count whole days only,1=Any day counts as 1*/
IF @DateFrom>@DateTo BEGIN
DECLARE @T DATETIME2=@DateTo,@F DATETIME2=@DateFrom
SELECT @DateFrom=@T,@DateTo=@F
END
DECLARE @Count AS INT=0,@DateAs DATETIME2=@DateFrom
WHILE @Date < @DateTo BEGIN
IF ((DATEPART(WEEKDAY,@Date)IN (1,7)AND @AdjustWeekEnds=1)
OR
EXISTS (SELECT * FROM Dates.CalendarHolidays WHERE CalendarDate=@Date AND CalendarFunction=@CalendarFunctionAND @AdjustHolidays=1))
BEGIN
SELECT @Count = @Count + 1
END
SELECT @Date=DATEADD(DAY,1,@Date)
END
RETURN (DATEDIFF(DAY,@DateFrom,@DateTo)-(@Count))+@AdjustMode
END
GO
SELECT Dates.GetDaysAdjusted('2014-01-01',0,'2014-01-31',1,1,1)--22
SELECT Dates.GetDaysAdjusted('2014-02-01',0,'2014-02-28',1,1,1)--20
SELECT Dates.GetDaysAdjusted('2014-05-01',0,'2014-05-02',1,1,1)--2
SELECT Dates.GetDaysAdjusted('2014-05-01',0,'2014-05-05',1,1,1)--2
SELECT Dates.GetDaysAdjusted('2014-05-01',0,'2014-05-06',1,1,1)--3