إذا قمت بسحب المقالات الأخرى ، فقد يكون لديك الجدول بالفعل ، وإذا لم يكن لديك ، استخدم الكود أدناه لإنشائه. هذه تستند إلى أيام العطل القياسية في إنجلترا وويلز. على الصفحة الرئيسية هناك وظائف للبلدان الأخرى.
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) 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.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
الآن لدينا البيانات ، يمكننا إنشاء وظيفة تتكرر خلال كل يوم من البداية وحتى نهاية الشهر اعتمادًا على بعض المتغيرات:
CREATE FUNCTION Dates.GetMonthAdjusted(@Month As DATETIME2,@CalendarFunction INT,@AdjustMode BIT,@AdjustWeekEnds BIT,@AdjustHolidays BIT) RETURNS INT AS BEGIN DECLARE @StartDate DATETIME2=CONVERT(DATE,DATEADD(DAY,1-DAY(@Month),@Month)) DECLARE @EndDate DATETIME2=DATEADD(DAY,-1,DATEADD(MONTH,1,@StartDate)),@Count AS INT=0,@Date As DATETIME2=@StartDate WHILE @Date < @EndDate BEGIN IF ((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 SELECT @Count = @Count + 1 END SET @Date=DATEADD(DAY, 1,@Date) END RETURN (DATEDIFF(DAY,@StartDate,@EndDate)-(@Count))+@AdjustMode ENDSELECT Dates.GetMonthAdjusted('2014-01-01',0,1,1,1) --22SELECT Dates.GetMonthAdjusted('2014-01-01',0,1,1,0) --23SELECT Dates.GetMonthAdjusted('2014-01-01',0,1,0,1) --30SELECT Dates.GetMonthAdjusted('2014-01-01',0,1,0,0) --31SELECT Dates.GetMonthAdjusted('2014-02-04',0,1,1,1) --20SELECT Dates.GetMonthAdjusted('2014-05-15',0,1,1,1) --20 (22-2)
