أضف أيام عمل حتى الآن في 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))
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'