Ajouter des jours ouvrables à ce jour dans une fonction SQL Server avec des options flexibles
Une fonction SQL Server flexible et réutilisable qui ajoutera un certain nombre de jours à une date avec la possibilité d'exclure les jours fériés ou les week-ends
Si vous avez vérifié les autres articles sur la date, 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))
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
GOMaintenant que nous avons les données, nous pouvons créer une fonction qui parcourt chaque jour du début à la fin et ajoute 1 à @Count où ce n'est pas un samedi, un dimanche ou dans votre base de données de jours fériés.
Nous avons reçu des commentaires selon lesquels la fonction précédente pourrait être assouplie, et en revenant à certains besoins supplémentaires des clients, nous avons mis à jour cela avec plus d'options:
- @AdjustDate - La date à modifier
- @CalenderFunction - La fonction de vacances que vous souhaitez utiliser (certains pays du Royaume-Uni ont des dates différentes, nous pouvons donc les stocker dans différentes fonctions)
- @AdjustDats - Le nombre de jours à ajouter ou à retirer de la date de base
- @AdjustMode - 0 pour ajouter des jours, 1 pour soustraire des jours
- @AdjustWeekend - Exclut les week-ends de vos calculs
- @AdjustHolidays - Exclut les jours fériés si la fonction vacances correspond
CREATE FUNCTION Dates.GetDateAdjusted(@AdjustDate AS DATETIME2,@CalendarFunction INT,@AdjustDays AS INT,@AdjustMode BIT,@AdjustWeekEnds BIT,@AdjustHolidays BIT) RETURNS DATETIME2 AS BEGIN
SELECT @AdjustDate=DATEADD(DAY,(CASE @AdjustMode WHEN 0 THEN -1 ELSE 1 END),@AdjustDate),
@AdjustDays=(CASE @AdjustMode WHEN 0 THEN @AdjustDays+1 ELSE (0-@AdjustDays)-1 END)
DECLARE @AdjustCount INT=0,@AdjustWorkDays INT=0,@Date DATETIME2=@AdjustDate
/*Add Days*/
WHILE @AdjustMode=0 AND @AdjustWorkDays < @AdjustDays
BEGIN
SET @AdjustCount=@AdjustCount+1
SET @Date=DATEADD(DAY,@AdjustCount,@AdjustDate)
IF NOT ((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
SET @AdjustWorkDays = @AdjustWorkDays + 1
END
END
/*Subtract Days*/
WHILE @AdjustMode=1 AND @AdjustWorkDays > @AdjustDays
BEGIN
SET @AdjustCount=@AdjustCount-1
SET @Date=DATEADD(DAY,@AdjustCount,@AdjustDate)
IF NOT ((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
SET @AdjustWorkDays = @AdjustWorkDays - 1
END
END
RETURN @Date
END
GO
SELECT Dates.GetDateAdjusted('2014-05-01',0,1,0,1,1)--'2014-05-02'
SELECT Dates.GetDateAdjusted('2014-05-01',0,2,0,0,0)--'2014-05-03'
SELECT Dates.GetDateAdjusted('2014-05-01',0,2,0,1,1)--'2014-05-06'
SELECT Dates.GetDateAdjusted('2014-05-01',0,3,0,1,1)--'2014-05-07'
SELECT Dates.GetDateAdjusted('2014-05-01',0,4,0,1,1)--'2014-05-08'
SELECT Dates.GetDateAdjusted('2014-05-01',0,5,0,1,1)--'2014-05-09'
SELECT Dates.GetDateAdjusted('2014-05-01',0,6,0,1,1)--'2014-05-12'