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))GODECLARE @D DATETIME2='1850-01-01'WHILE @D<='2099-12-31' BEGININSERT INTO Dates.Calendar(CalendarDate) SELECT @DSET @D=DATEADD(DAY,1,@D)ENDGOCREATE 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.CalendarHolidaysSELECT CalendarDate,0,'New Years Day' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=1 AND DATEPART(DAY,CalendarDate)=1 UNION --New Years DaySELECT CalendarDate,0,'Good Friday' FROM Dates.Calendar WHERE CalendarDate=DATEADD(DAY,-2,Dates.GetEasterDate(DATEPART(YEAR,CalendarDate))) UNION --Good FridaySELECT CalendarDate,0,'Easter Monday' FROM Dates.Calendar WHERE CalendarDate=DATEADD(DAY,1,Dates.GetEasterDate(DATEPART(YEAR,CalendarDate))) UNION --Easter MondaySELECT CalendarDate,0,'May Holidays' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=5 AND WeekDayID=2 AND (CalendarCA=1 OR CalendarCD=1)UNION --May HolidaysSELECT CalendarDate,0,'August Holidays' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=8 AND WeekDayID=2 AND (CalendarCD=1) UNION --August HolidaysSELECT CalendarDate,0,'Christmas Day' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=12 AND DATEPART(DAY,CalendarDate)=25 UNION --Christmas DaySELECT CalendarDate,0,'Boxing Day' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=12 AND DATEPART(DAY,CalendarDate)=26 --Boxing DayGO 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 BEGINDECLARE @T DATETIME2=@DateTo,@F DATETIME2=@DateFromSELECT @DateFrom=@T,@DateTo=@FENDDECLARE @Count AS INT=0,@DateAs DATETIME2=@DateFromWHILE @Date < @DateTo BEGINIF ((DATEPART(WEEKDAY,@Date)IN (1,7)AND @AdjustWeekEnds=1)OREXISTS (SELECT * FROM Dates.CalendarHolidays WHERE CalendarDate=@Date AND CalendarFunction=@CalendarFunctionAND @AdjustHolidays=1))BEGINSELECT @Count = @Count + 1ENDSELECT @Date=DATEADD(DAY,1,@Date)ENDRETURN (DATEDIFF(DAY,@DateFrom,@DateTo)-(@Count))+@AdjustModeENDGOSELECT Dates.GetDaysAdjusted('2014-01-01',0,'2014-01-31',1,1,1)--22SELECT Dates.GetDaysAdjusted('2014-02-01',0,'2014-02-28',1,1,1)--20SELECT Dates.GetDaysAdjusted('2014-05-01',0,'2014-05-02',1,1,1)--2SELECT Dates.GetDaysAdjusted('2014-05-01',0,'2014-05-05',1,1,1)--2SELECT Dates.GetDaysAdjusted('2014-05-01',0,'2014-05-06',1,1,1)--3