Date - Aggiunta di giorni lavorativi a una data esclusa la festività

Creare una funzione che aggiunga un numero di giorni (esclusi i giorni festivi) a una data. Ciò è particolarmente utile quando utilizzato come parte dei sistemi di fatturazione o previsione.

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.

Create Table

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 a @Count dove non è un sabato, domenica o nel tuo database dei giorni festivi.

Abbiamo ricevuto un feedback in merito al fatto che la precedente funzione potesse essere resa flessibile e una rivisitazione di alcune esigenze aggiuntive del cliente, che abbiamo aggiornato con più opzioni:

@AdjustDate: la data che desideri modificare
@CalenderFunction - La funzione vacanze che desideri utilizzare (alcuni Paesi nel Regno Unito hanno date diverse, quindi possiamo memorizzarli in diverse funzioni)
@AdjustDats: il numero di giorni da aggiungere o rimuovere dalla data di base
@AdjustMode - 0 per aggiungere giorni, 1 per sottrarre giorni
@AdjustWeekend - Esclude i fine settimana dai tuoi calcoli
@AdjustHolidays: esclude le festività se la funzione festività corrisponde

SQL

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'