Jours ouvrables entre les dates dans une fonction SQL Server avec des options flexibles
Une fonction SQL Server flexible et réutilisable qui calculera le nombre de jours entre deux dates avec la possibilité d'exclure les jours fériés ou les week-ends
Si vous avez vérifié les autres articles, vous avez peut-être déjà le tableau, sinon utilisez le code ci-dessous pour le créer. Ceux-ci sont basés sur des vacances standard en Angleterre et au Pays de Galles. Sur la page principale, il existe des fonctions pour d'autres pays.
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 Maintenant que nous avons les données, nous pouvons créer une fonction qui parcourt chaque jour du début à la fin et ajoute 1 en fonction des variables connectées comme ci-dessous:
- @DateFrom - La date de début de votre calcul
- @CalendarFunction - La fonction de type de vacances que vous souhaitez utiliser
- @DateTo - La fin de la plage de dates que vous souhaitez utiliser
- @AdjustMode - Calculez les jours entre les dates comme 0 exclusif ou 1 inclusif du premier jour.
- @AdjustWeekend - Exclut les week-ends de vos calculs
- @AdjustHolidays - Exclut les jours fériés si la fonction vacances correspond
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