أيام العمل بين التواريخ في SQL Server Function مع خيارات مرنة
وظيفة 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