روزهای کاری بین تاریخها در یک عملکرد SQL Server با گزینه های انعطاف پذیر
عملکرد SQL Server قابل انعطاف و قابل استفاده مجدد که تعداد روزهای بین دو تاریخ را با گزینه محرومیت از تعطیلات یا آخر هفته محاسبه می کند.
اگر مطالب دیگر را بررسی کرده اید ، می توانید جدول را دراختیار داشته باشید ، در غیر اینصورت از کد زیر برای ایجاد آن استفاده کنید. اینها در تعطیلات استاندارد در انگلیس و ولز است. در صفحه اصلی توابع سایر کشورها وجود دارد.
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))GODECLARE @D DATETIME2='1850-01-01'WHILE @D<='2099-12-31' BEGININSERT INTO Dates.Calendar(CalendarDate) SELECT @DSET @D=DATEADD(DAY,1,@D)ENDGOCREATE 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.CalendarHolidaysSELECT CalendarDate,0,'New Years Day' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=1 AND DATEPART(DAY,CalendarDate)=1 UNION --New Years DaySELECT CalendarDate,0,'Good Friday' FROM Dates.Calendar WHERE CalendarDate=DATEADD(DAY,-2,Dates.GetEasterDate(DATEPART(YEAR,CalendarDate))) UNION --Good FridaySELECT CalendarDate,0,'Easter Monday' FROM Dates.Calendar WHERE CalendarDate=DATEADD(DAY,1,Dates.GetEasterDate(DATEPART(YEAR,CalendarDate))) UNION --Easter MondaySELECT CalendarDate,0,'May Holidays' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=5 AND WeekDayID=2 AND (CalendarCA=1 OR CalendarCD=1)UNION --May HolidaysSELECT CalendarDate,0,'August Holidays' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=8 AND WeekDayID=2 AND (CalendarCD=1) UNION --August HolidaysSELECT CalendarDate,0,'Christmas Day' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=12 AND DATEPART(DAY,CalendarDate)=25 UNION --Christmas DaySELECT CalendarDate,0,'Boxing Day' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=12 AND DATEPART(DAY,CalendarDate)=26 --Boxing DayGO اکنون داده ها را در اختیار داریم ، می توانیم تابعی را ایجاد کنیم که هر روز از آغاز تا پایان به پایان می رسد و 1 مورد وابسته به متغیرهای وصل شده در زیر را اضافه می کنیم:
- DateFrom - تاریخ شروع محاسبه شما
- CalendarFunction - تابعی از نوع تعطیلات که می خواهید استفاده کنید
- DateTo - پایان دامنه تاریخی که می خواهید استفاده کنید
- AdjustMode - روزهای بین تاریخ را به عنوان 0 اختصاصی یا 1 شامل روز اول محاسبه کنید.
- AdjustWeekend - آخر هفته را از محاسبات خود خارج می کند
- AdjustHolidays - اگر عملکرد تعطیلات مطابقت داشته باشد ، تعطیلات را مستثنی نمی کند
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 BEGINDECLARE @T DATETIME2=@DateTo,@F DATETIME2=@DateFromSELECT @DateFrom=@T,@DateTo=@FENDDECLARE @Count AS INT=0,@DateAs DATETIME2=@DateFromWHILE @Date < @DateTo BEGINIF ((DATEPART(WEEKDAY,@Date)IN (1,7)AND @AdjustWeekEnds=1)OREXISTS (SELECT * FROM Dates.CalendarHolidays WHERE CalendarDate=@Date AND CalendarFunction=@CalendarFunctionAND @AdjustHolidays=1))BEGINSELECT @Count = @Count + 1ENDSELECT @Date=DATEADD(DAY,1,@Date)ENDRETURN (DATEDIFF(DAY,@DateFrom,@DateTo)-(@Count))+@AdjustModeENDGOSELECT Dates.GetDaysAdjusted('2014-01-01',0,'2014-01-31',1,1,1)--22SELECT Dates.GetDaysAdjusted('2014-02-01',0,'2014-02-28',1,1,1)--20SELECT Dates.GetDaysAdjusted('2014-05-01',0,'2014-05-02',1,1,1)--2SELECT Dates.GetDaysAdjusted('2014-05-01',0,'2014-05-05',1,1,1)--2SELECT Dates.GetDaysAdjusted('2014-05-01',0,'2014-05-06',1,1,1)--3