روزهای کاری را به روز در یک عملکرد سرور SQL با گزینه های انعطاف پذیر اضافه کنید

یک عملکرد SQL Server قابل انعطاف و قابل استفاده مجدد که می تواند چند روز به تاریخ اضافه کند با گزینه ای برای حذف تعطیلات یا آخر هفته

اگر مقالات تاریخ دیگر را بررسی کرده اید ، می توانید جدول را دراختیار داشته باشید ، در غیر اینصورت از کد زیر برای ایجاد آن استفاده کنید.

اینها در تعطیلات استاندارد در انگلیس و ولز است.

در صفحه اصلی توابع سایر کشورها وجود دارد.

بیشتر: استفاده از SQL Server Dates

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

اکنون داده ها را داریم ، می توانیم تابعی را ایجاد کنیم که هر روز از ابتدا تا انتها حلقه کند و 1 را بهCount اضافه کنیم که در آن یک شنبه ، یکشنبه یا در پایگاه داده شما از روزهای تعطیل نیست.

ما بازخوردی داشتیم مبنی بر اینکه عملکرد قبلی قابل انعطاف است و بازنگری در برخی نیازهای مشتری اضافی ، ما این گزینه را با گزینه های بیشتر به روز کرده ایم:

AdjustDate - تاریخی که می خواهید تغییر دهید
CalenderFunction - عملکرد تعطیلاتی که می خواهید استفاده کنید (بعضی از کشورها در انگلستان دارای تاریخ های مختلفی هستند ، بنابراین می توانیم آنها را در توابع مختلف ذخیره کنیم)
AdjustDats - تعداد روزهای اضافه یا حذف از تاریخ پایه
AdjustMode - 0 برای روزهای اضافه ، 1 روز برای تفریق
AdjustWeekend - آخر هفته را از محاسبات خود خارج می کند
AdjustHolidays - اگر عملکرد تعطیلات مطابقت داشته باشد ، تعطیلات را مستثنی نمی کند

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'