Tarihler - Tatiller hariç bir tarihe iş günü ekleme

Bir tarihe birkaç gün (tatil günleri hariç) eklenecek bir işlev oluşturun. Bu, faturalama veya tahmin sistemlerinin bir parçası olarak kullanıldığında özellikle yararlıdır.

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.

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

İşlev Oluştur

Şimdi verilere sahibiz, her güne baştan sona kadar bir döngü oluşturabilir ve bir Cumartesi, Pazar veya tatil günlerinizdeki veritabanında 1'den @Count'a ekleriz.

Önceki fonksiyonun esnek hale getirilebileceğine dair bazı geri bildirimlerimiz ve bazı ek müşteri ihtiyaçlarının yeniden gözden geçirilmesi, bunu daha fazla seçenekle güncelledik:

@AdjustDate - Değiştirmek istediğiniz tarih
@CalenderFunction - Kullanmak istediğiniz tatil fonksiyonu (İngiltere'deki bazı ülkeler farklı tarihlere sahiptir, bu yüzden bunları farklı işlevlerde saklayabiliriz)
@AdjustDats - Temel tarihe eklenecek veya kaldırılacak gün sayısı
@AdjustMode - 0 gün, çıkarma günü için 1
@AdjustWeekend - Hesaplamalarınızdan hafta sonlarını hariç tutar
@AdjustHolidays - Tatil işlevi eşleştiğinde tatilleri hariç tutar

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'