Calcolo dei giorni lavorativi tra due date escluse le festività in SQL Server

Creare una funzione che calcoli il numero di giorni lavorativi (esclusi i giorni festivi) tra due date.

Crea una tabella delle festività

Se hai controllato gli altri articoli allora potresti già avere la tabella, altrimenti usa il codice qui sotto per crearlo. Questi sono basati su vacanze standard in Inghilterra e Galles. Nella pagina principale ci sono funzioni per altri Paesi.

SQL

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)

ENDGO
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

Crea funzione

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

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

SQL

CREATE FUNCTION Dates.GetDaysAdjusted(@DateFrom As 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,@Date As 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=@CalendarFunction AND @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