SQL Server'da tatiller hariç iki tarih arasındaki iş günlerinin hesaplanması

İki tarih arasında iş günlerinin sayısını (tatil günleri hariç) hesaplayacak bir işlev oluşturun.

Tatil Tablosu Oluştur

Diğer makaleleri teslim ettiyseniz, tabloyu zaten sahip olabilirsiniz, daha sonra oluşturmak için aşağıdaki kodu kullanamazsınız. Bunlar İngiltere ve Galler'deki standart tatillere dayanmaktadır. Ana sayfada diğer Ülkeler için fonksiyonlar vardır.

SQL

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

İşlev Oluştur

Şimdi verilere sahibiz, her güne başlangıçtan bitiş tarihlerine kadar döngü uygulayan bir işlev oluşturabilir ve aşağıdaki gibi takılı değişkenlere 1 bağımlılık ekleyebiliriz:

@DateFrom - Hesaplamanızın başlangıç tarihi
@CalendarFunction - Kullanmak istediğiniz tatil tipi işlevi
@DateTo - Kullanmak istediğiniz tarih aralığının sonu
@AdjustMode - Tarihler arasındaki ilk günü 0 Özel veya 1 Dahil olan günleri hesaplayın.
@AdjustWeekend - Hesaplamalarınızdan hafta sonlarını hariç tutar
@AdjustHolidays - Tatil işlevi eşleştiğinde tatilleri hariç tutar

SQL

CREATE FUNCTION Dates.GetDaysAdjusted(@DateFrom As 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,@Date As 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=@CalendarFunction AND @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