Esnek seçeneklerle SQL Server İşlevindeki tarihler arasındaki iş günleri

Tatilleri veya hafta sonlarını hariç tutma seçeneği ile iki tarih arasındaki gün sayısını hesaplayan esnek ve yeniden kullanılabilir bir SQL Server işlevi

Diğer makalelere göz attıysanız, tabloya zaten sahip olabilirsiniz, değilse, oluşturmak için aşağıdaki kodu kullanın. Bunlar İngiltere ve Galler'deki standart tatillere dayanmaktadır. Ana sayfada diğer Ülkeler için işlevler vardır.

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
GO

Şimdi verilerimiz var, başlangıçtan bitiş tarihlerine kadar her gün dolaşan ve aşağıdaki gibi takılan değişkenlere bağlı olarak 1 ekleyen bir işlev oluşturabiliriz:

@DateFrom - Hesaplamanızın başlangıç tarihi
@CalendarFunction - Kullanmak istediğiniz tatil türü işlevi
@DateTo - Kullanmak istediğiniz tarih aralığının sonu
@AdjustMode - Tarihler arasındaki günleri 0 Özel veya İlk günün 1'i dahil olarak hesaplayın.
@AdjustWeekend - Hafta sonlarını hesaplamalarınızdan hariç tutar
@AdjustHolidays - Tatil işlevi eşleşirse tatil günleri hariçtir

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 BEGIN
DECLARE @T DATETIME2=@DateTo,@F DATETIME2=@DateFrom
SELECT @DateFrom=@T,@DateTo=@F
END
DECLARE @Count AS INT=0,@DateAs 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=@CalendarFunctionAND @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