تضمنت معظم الأعمال التي قمنا بها سابقًا العمل مع التواريخ والأعمار ومعلومات أخرى متنوعة. أشياء مثل حساب العمر ليست بهذه البساطة. لقد كتبنا مجموعة مختارة من المقالات سابقًا ، لكن يتم استبدالها الآن بوظائف أكثر مرونة لتقليل عدد الوظائف التي تحتاجها.
سنقوم في هذه المقالة بإعداد جدول التقويم القابل لإعادة الاستخدام وجدول عطلة البنوك ، ثم نشره. سيتم كتابة وظائف مختلفة كمقالات منفصلة حيث يمكنك بعد ذلك الحصول على مزيد من المعلومات.
نميل إلى استخدام قاعدة بيانات "الأداة المساعدة" المشتركة مع جميع وظائفنا فيها لتقليل احتياجات التحكم في التغيير والبيانات المتسقة.
إذا كان لديك قاعدة بيانات مشتركة ، أو تعرف أين تريد تخزين هذه الوظائف ، فلا تتردد ، ولكن تم تصميم جميع التعليمات البرمجية من هنا لتشغيلها في برنامج نصي واحد في SQL Server 2016.
ملحوظة! - قد يكون من الأسهل إنشاء قاعدة البيانات الخاصة بك في SSMS
تلميح! - اضبط الخيارات على طريقة بسيطة لتجنب انتفاخ ملف السجل ، ومن غير المرجح أن تكون البيانات مهمة للأعمال.
CREATE DATABASE [Utilities] CONTAINMENT = NONE ON PRIMARY ( NAME = N'Utilities',FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQLSERVER13\MSSQL\DATA\Utilities.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB) LOG ON ( NAME = N'Utilities_log',FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQLSERVER13\MSSQL\DATA\Utilities_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB)GO
CREATE DATABASE [Utilities]
CONTAINMENT = NONE ON PRIMARY (
NAME = N'Utilities',FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQLSERVER13\MSSQL\DATA\Utilities.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB)
LOG ON (
NAME = N'Utilities_log',FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQLSERVER13\MSSQL\DATA\Utilities_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB)
GO
لتسهيل الإدارة ، أنشأنا مخططًا يسمى التواريخ ، وقم بتخزين جميع المعلومات ذات الصلة في هذا. فكر في المخطط بهذا المعنى كحاوية أو مساحة اسم للكائنات ذات الصلة.
تلميح! - عند إنشاء مخطط في عبارات SQL الدفعية ، لفه في عبارة sp_executesql لتجنب النظام الذي يشكو من أنه البيان الوحيد في الدُفعة. إذا كان موجودا ، فقم بلفه في كتلة محاولة للسماح بتشغيل الشفرة.
USE UtilitiesGOSET NOCOUNT ON;BEGIN TRYEXEC sp_executesql N'CREATE SCHEMA Dates'END TRY BEGIN CATCH END CATCH;GOBEGIN TRYEXEC sp_executesql N'CREATE SCHEMA Test'END TRY BEGIN CATCH END CATCH;GO
USE Utilities
SET NOCOUNT ON;
BEGIN TRY
EXEC sp_executesql N'CREATE SCHEMA Dates'
END TRY BEGIN CATCH END CATCH;
EXEC sp_executesql N'CREATE SCHEMA Test'
هناك وظيفتان مشتركتان سيتم استخدامهما بشكل أكبر ، لذلك سنوفر الرمز والرابط ، مع توضيح في كل صفحة. هناك بعض الأشياء التي يجب ملاحظتها:
حسابات سنة DateDiff القياسية هي سنة بحتة ، لذا لحساب العمر الذي تحتاجه للتحقق مما إذا كان هذا التاريخ أعلى أم لا. كانت وظيفة GetLeapYear الخاصة بنا هي الطريقة الأكثر منطقية للعمل بها إذا احتجنا إلى إضافة يوم.
يمكن أن يفشل تاريخ عيد الفصح إذا لم تستخدم الدالة DateFromParts الجديدة ، بسبب عدم وجود حشوة نهارية.
CREATE FUNCTION Dates.GetLeapYear(@Date DATETIME2) RETURNS BIT AS BEGINDECLARE @Ret BIT=(CASE WHEN DATEPART(YEAR,@Date)%4<>0 OR (DATEPART(YEAR,@Date)%100=0 AND DATEPART(YEAR,@Date)%400<>0) THEN 0 ELSE 1 END)RETURN @RetENDGO
CREATE FUNCTION Dates.GetLeapYear(@Date DATETIME2) RETURNS BIT AS BEGIN
DECLARE @Ret BIT=(CASE WHEN DATEPART(YEAR,@Date)%4<>0 OR (DATEPART(YEAR,@Date)%100=0 AND DATEPART(YEAR,@Date)%400<>0) THEN 0 ELSE 1 END)
RETURN @Ret
END
CREATE FUNCTION Dates.GetAge(@Date DATETIME2,@Until DATETIME2) RETURNS INT AS BEGINIF @Until IS NULL SET @Until=CONVERT(DATE,GETDATE())DECLARE @Age INT=DATEDIFF(YEAR,@Date,@Until)+(CASE WHEN DATEPART(DAYOFYEAR,@Date)>(DATEPART(DAYOFYEAR,@Until)+(CASE WHEN dbo.GetLeapYear(@Until)=1 AND DATEPART(DAYOFYEAR,@Until)>59 THEN -1 ELSE 0 END))THEN -1 ELSE 0 END)RETURN @AgeENDGO
CREATE FUNCTION Dates.GetAge(@Date DATETIME2,@Until DATETIME2) RETURNS INT AS BEGIN
IF @Until IS NULL SET @Until=CONVERT(DATE,GETDATE())
DECLARE @Age INT=DATEDIFF(YEAR,@Date,@Until)
+(CASE WHEN DATEPART(DAYOFYEAR,@Date)>
(DATEPART(DAYOFYEAR,@Until)+(CASE WHEN dbo.GetLeapYear(@Until)=1 AND DATEPART(DAYOFYEAR,@Until)>59 THEN -1 ELSE 0 END))
THEN -1 ELSE 0 END)
RETURN @Age
CREATE FUNCTION Dates.DatePad(@PadValue NVARCHAR(100),@PadLen INT) RETURNS NVARCHAR(4) AS BEGINRETURN ISNULL(REPLICATE('0',@PadLen-LEN(@PadValue))+@PadValue,LEFT(@PadValue,@PadLen))ENDGO
CREATE FUNCTION Dates.DatePad(@PadValue NVARCHAR(100),@PadLen INT) RETURNS NVARCHAR(4) AS BEGIN
RETURN ISNULL(REPLICATE('0',@PadLen-LEN(@PadValue))+@PadValue,LEFT(@PadValue,@PadLen))
CREATE FUNCTION Dates.DateFromParts(@Year SMALLINT,@Month SMALLINT,@Day SMALLINT) RETURNS DATETIME2 AS BEGINDECLARE @Date DATETIME2=Dates.DatePad(@Year,4)+'-'+Dates.DatePad(@Month,2)+'-'+Dates.DatePad(@Day,2)RETURN @DateENDGO
CREATE FUNCTION Dates.DateFromParts(@Year SMALLINT,@Month SMALLINT,@Day SMALLINT) RETURNS DATETIME2 AS BEGIN
DECLARE @Date DATETIME2=Dates.DatePad(@Year,4)+'-'+Dates.DatePad(@Month,2)+'-'+Dates.DatePad(@Day,2)
RETURN @Date
CREATE FUNCTION Dates.GetEasterDate(@Year INT) RETURNS DATETIME2 AS BEGIN/*Calculate date of easter based on Year passed - adjusted from Wikipedia*/Declare @GregorianEaster DATETIME2Declare @a INT, @b INT, @c INT, @d INT, @e INT, @f INT, @g INT, @h INT, @i INT, @k INT, @L INT, @m INT, @Month INT, @Day INTSET @a = @Year % 19SET @b = floor(@Year/ 100)SET @c = @Year % 100SET @d = floor(@b/ 4)SET @e = @b % 4SET @f = floor((@b+ 8) / 25)SET @g = floor((@b- @f + 1)/3)SET @h = (19*@a+ @b - @d - @g + 15) % 30SET @i = floor(@c/ 4)SET @k = @c % 4SET @L = (32 + 2*@e + 2*@i- @h - @k) % 7SET @m = floor((@a+ 11*@h + 22*@L)/ 451)SET @month = floor((@h+ @L - 7*@m + 114) / 31)SET @day = (@h + @L - 7*@m + 114) % 31 + 1SET @GregorianEaster = Dates.DateFromParts(@Year,@Month,@Day)RETURN @GregorianEaster ENDGO
CREATE FUNCTION Dates.GetEasterDate(@Year INT) RETURNS DATETIME2 AS BEGIN
/*Calculate date of easter based on Year passed - adjusted from Wikipedia*/
Declare @GregorianEaster DATETIME2
Declare @a INT, @b INT, @c INT, @d INT, @e INT, @f INT, @g INT, @h INT, @i INT, @k INT, @L INT, @m INT, @Month INT, @Day INT
SET @a = @Year % 19
SET @b = floor(@Year/ 100)
SET @c = @Year % 100
SET @d = floor(@b/ 4)
SET @e = @b % 4
SET @f = floor((@b+ 8) / 25)
SET @g = floor((@b- @f + 1)/3)
SET @h = (19*@a+ @b - @d - @g + 15) % 30
SET @i = floor(@c/ 4)
SET @k = @c % 4
SET @L = (32 + 2*@e + 2*@i- @h - @k) % 7
SET @m = floor((@a+ 11*@h + 22*@L)/ 451)
SET @month = floor((@h+ @L - 7*@m + 114) / 31)
SET @day = (@h + @L - 7*@m + 114) % 31 + 1
SET @GregorianEaster = Dates.DateFromParts(@Year,@Month,@Day)
RETURN @GregorianEaster
للجدولة والتكرار ، نستخدم جدول الجدولة الخاص بنا. يتيح لنا ذلك حساب عدد مرات تكرار شيء ما.
CREATE TABLE Dates.CalendarFrequency(FrequencyID NVARCHAR(2),FrequencyName NVARCHAR(100),FrequencyOrder INT)GOINSERT INTO Dates.CalendarFrequencySELECT '','Once',0 UNIONSELECT '7','Weekly',1 UNIONSELECT '14','Fortnightly',2 UNIONSELECT '32','Monthly (Same Day 1st,15th,28th etc)',3 UNIONSELECT '1','Monthly (First Monday,Wednesday etc)',4 UNIONSELECT '2','Monthly (Second Monday,Wednesday etc)',5 UNIONSELECT '3','Monthly (Third Monday,Wednesday etc)',6 UNIONSELECT '4','Monthly (Fourth Monday,Wednesday etc)',10 UNIONSELECT '-1','Monthly (Last Monday,Wednesday etc)',7 UNIONSELECT '-2','Monthly (Second Last Monday,Wednesday etc)',8 UNIONSELECT '-3','Monthly (Third Last Monday,Wednesday etc)',9 UNIONSELECT '-4','Monthly (Fourth Last Monday,Wednesday etc)',11ORDER BY 3GO
CREATE TABLE Dates.CalendarFrequency(FrequencyID NVARCHAR(2),FrequencyName NVARCHAR(100),FrequencyOrder INT)
INSERT INTO Dates.CalendarFrequency
SELECT '','Once',0 UNION
SELECT '7','Weekly',1 UNION
SELECT '14','Fortnightly',2 UNION
SELECT '32','Monthly (Same Day 1st,15th,28th etc)',3 UNION
SELECT '1','Monthly (First Monday,Wednesday etc)',4 UNION
SELECT '2','Monthly (Second Monday,Wednesday etc)',5 UNION
SELECT '3','Monthly (Third Monday,Wednesday etc)',6 UNION
SELECT '4','Monthly (Fourth Monday,Wednesday etc)',10 UNION
SELECT '-1','Monthly (Last Monday,Wednesday etc)',7 UNION
SELECT '-2','Monthly (Second Last Monday,Wednesday etc)',8 UNION
SELECT '-3','Monthly (Third Last Monday,Wednesday etc)',9 UNION
SELECT '-4','Monthly (Fourth Last Monday,Wednesday etc)',11
ORDER BY 3
تم إعداد جدول التقويم الخاص بنا ليتم تشغيله لأي فترة زمنية ضمن نطاق DATETIME2. بشكل عام ، يمنحك الرمز أدناه نطاقًا يغطي معظم السيناريوهات.
CalendarCA و CalendarCD هي أرقام الأسبوع تصاعدي وتنازلي لكل شهر ، وتستخدم في إشارة تبادلية مقابل جدول التردد لدينا أعلاه.
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)ENDGO
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))
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)
لقد قمنا بتصميم جدول الإجازات ليكون مرنًا قدر الإمكان ، والسماح بتخزين خيارات مختلفة عن طريق إضافة عمود "التقويم". أدناه ، قمنا بتخزين العطلات الخاصة بإنجلترا وويلز واسكتلندا وأيرلندا الشمالية بشكل منفصل ، مع الإشارة إلى جدول التقويم الذي تم إنشاؤه بالفعل.
من الناحية النظرية ، يمكن أن يكون لديك عدد المخططات التي تريدها.
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.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/*Scotish Holidays*/INSERT INTO Dates.CalendarHolidaysSELECT CalendarDate,1,'New Years Day' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=1AND DATEPART(DAY,CalendarDate)=1 UNION --New Years DaySELECT CalendarDate,1,'2nd' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=1 AND DATEPART(DAY,CalendarDate)=2 UNION --New Years DaySELECT CalendarDate,1,'Good Friday' FROM Dates.Calendar WHERE CalendarDate=DATEADD(DAY,-2,Dates.GetEasterDate(DATEPART(YEAR,CalendarDate)))UNION--Good FridaySELECT CalendarDate,1,'May Holidays' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=5 AND WeekDayID=2 AND (CalendarCA=1 OR CalendarCD=1)UNION--May HolidaysSELECT CalendarDate,1,'August Holidays' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=8 AND WeekDayID=2 AND (CalendarCA=1) UNION--August HolidaysSELECT CalendarDate,1,'St Andrews' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=11 AND DATEPART(DAY,CalendarDate)=30 UNION --St AndrewsSELECT CalendarDate,1,'Christmas Day' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=12 AND DATEPART(DAY,CalendarDate)=25 UNION --Christmas DaySELECT CalendarDate,1,'Boxing Day' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=12 AND DATEPART(DAY,CalendarDate)=26 --Boxing DayGO/*Northern Irish Holidays*/INSERT INTO Dates.CalendarHolidaysSELECT CalendarDate,2,'New Years Day' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=1 AND DATEPART(DAY,CalendarDate)=1 UNION --New Years DaySELECT CalendarDate,2,'Good Friday' FROM Dates.Calendar WHERE CalendarDate=DATEADD(DAY,-2,Dates.GetEasterDate(DATEPART(YEAR,CalendarDate))) UNION--Good FridaySELECT CalendarDate,2,'Easter Monday' FROM Dates.Calendar WHERE CalendarDate=DATEADD(DAY,1,Dates.GetEasterDate(DATEPART(YEAR,CalendarDate))) UNION--Easter MondaySELECT CalendarDate,2,'May Holidays' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=5 AND WeekDayID=2 AND (CalendarCA=1 OR CalendarCD=1)UNION--May HolidaysSELECT CalendarDate,2,'Battle of the Boyne' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=7 AND DATEPART(DAY,CalendarDate)=12 UNION --Battle of the BoyneSELECT CalendarDate,2,'August Holidays' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=8 AND WeekDayID=2 AND (CalendarCA=1) UNION--August HolidaysSELECT CalendarDate,2,'Christmas Day' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=12 AND DATEPART(DAY,CalendarDate)=25 UNION --Christmas DaySELECT CalendarDate,2,'Boxing Day' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=12 AND DATEPART(DAY,CalendarDate)=26 --Boxing DayGO
CREATE TABLE Dates.CalendarHolidays(CalendarDate DATETIME2 NOT NULL,CalendarFunction INT NOT NULL,HolidayType VARCHAR(100) NULL,CONSTRAINT PK_Holidays_Id PRIMARY KEY(CalendarDate,CalendarFunction))
/*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
/*Scotish Holidays*/
SELECT CalendarDate,1,'New Years Day' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=1AND DATEPART(DAY,CalendarDate)=1 UNION --New Years Day
SELECT CalendarDate,1,'2nd' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=1 AND DATEPART(DAY,CalendarDate)=2 UNION --New Years Day
SELECT CalendarDate,1,'Good Friday' FROM Dates.Calendar WHERE CalendarDate=DATEADD(DAY,-2,Dates.GetEasterDate(DATEPART(YEAR,CalendarDate)))UNION--Good Friday
SELECT CalendarDate,1,'May Holidays' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=5 AND WeekDayID=2 AND (CalendarCA=1 OR CalendarCD=1)UNION--May Holidays
SELECT CalendarDate,1,'August Holidays' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=8 AND WeekDayID=2 AND (CalendarCA=1) UNION--August Holidays
SELECT CalendarDate,1,'St Andrews' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=11 AND DATEPART(DAY,CalendarDate)=30 UNION --St Andrews
SELECT CalendarDate,1,'Christmas Day' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=12 AND DATEPART(DAY,CalendarDate)=25 UNION --Christmas Day
SELECT CalendarDate,1,'Boxing Day' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=12 AND DATEPART(DAY,CalendarDate)=26 --Boxing Day
/*Northern Irish Holidays*/
SELECT CalendarDate,2,'New Years Day' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=1 AND DATEPART(DAY,CalendarDate)=1 UNION --New Years Day
SELECT CalendarDate,2,'Good Friday' FROM Dates.Calendar WHERE CalendarDate=DATEADD(DAY,-2,Dates.GetEasterDate(DATEPART(YEAR,CalendarDate))) UNION--Good Friday
SELECT CalendarDate,2,'Easter Monday' FROM Dates.Calendar WHERE CalendarDate=DATEADD(DAY,1,Dates.GetEasterDate(DATEPART(YEAR,CalendarDate))) UNION--Easter Monday
SELECT CalendarDate,2,'May Holidays' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=5 AND WeekDayID=2 AND (CalendarCA=1 OR CalendarCD=1)UNION--May Holidays
SELECT CalendarDate,2,'Battle of the Boyne' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=7 AND DATEPART(DAY,CalendarDate)=12 UNION --Battle of the Boyne
SELECT CalendarDate,2,'August Holidays' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=8 AND WeekDayID=2 AND (CalendarCA=1) UNION--August Holidays
SELECT CalendarDate,2,'Christmas Day' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=12 AND DATEPART(DAY,CalendarDate)=25 UNION --Christmas Day
SELECT CalendarDate,2,'Boxing Day' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=12 AND DATEPART(DAY,CalendarDate)=26 --Boxing Day
هناك ثلاث وظائف مستخدمة تجمعها بطرق مختلفة ، مع خيارات متشابهة للغاية.
CREATE FUNCTION Dates.GetDateAdjusted(@AdjustDate AS DATETIME2,@CalendarFunction INT,@AdjustDays AS INT,@AdjustMode BIT,@AdjustWeekEnds BIT,@AdjustHolidays BIT) RETURNS DATETIME2 AS BEGINSELECT @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 < @AdjustDaysBEGIN 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 ENDEND/*Subtract Days*/WHILE @AdjustMode=1 AND @AdjustWorkDays > @AdjustDaysBEGIN 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 ENDENDRETURN @DateEND GO
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))
SET @AdjustWorkDays = @AdjustWorkDays + 1
/*Subtract Days*/
WHILE @AdjustMode=1 AND @AdjustWorkDays > @AdjustDays
SET @AdjustCount=@AdjustCount-1
IF NOT ((DATEPART(WEEKDAY,@Date) IN (1,7)AND @AdjustWeekEnds=1)
SET @AdjustWorkDays = @AdjustWorkDays - 1
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))+@AdjustModeENDGO
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 BEGIN
DECLARE @T DATETIME2=@DateTo,@F DATETIME2=@DateFrom
SELECT @DateFrom=@T,@DateTo=@F
DECLARE @Count AS INT=0,@DateAs DATETIME2=@DateFrom
WHILE @Date < @DateTo BEGIN
IF ((DATEPART(WEEKDAY,@Date)IN (1,7)AND @AdjustWeekEnds=1)
EXISTS (SELECT * FROM Dates.CalendarHolidays WHERE CalendarDate=@Date AND CalendarFunction=@CalendarFunctionAND @AdjustHolidays=1))
SELECT @Count = @Count + 1
SELECT @Date=DATEADD(DAY,1,@Date)
RETURN (DATEDIFF(DAY,@DateFrom,@DateTo)-(@Count))+@AdjustMode
CREATE FUNCTION Dates.GetMonthAdjusted(@Month As DATETIME2,@CalendarFunction INT,@AdjustMode BIT,@AdjustWeekEnds BIT,@AdjustHolidays BIT)RETURNS INT AS BEGINDECLARE @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=@StartDateWHILE @Date < @EndDateBEGINIF ((DATEPART(WEEKDAY,@Date) IN(1,7) AND @AdjustWeekEnds=1)OREXISTS (SELECT * FROM Dates.CalendarHolidays WHERE CalendarDate=@Date AND CalendarFunction=@CalendarFunction AND @AdjustHolidays=1))BEGINSELECT @Count = @Count + 1ENDSET @Date=DATEADD(DAY, 1,@Date)ENDRETURN (DATEDIFF(DAY,@StartDate,@EndDate)-(@Count))+@AdjustModeEND
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
IF ((DATEPART(WEEKDAY,@Date) IN(1,7) AND @AdjustWeekEnds=1)
SET @Date=DATEADD(DAY, 1,@Date)
RETURN (DATEDIFF(DAY,@StartDate,@EndDate)-(@Count))+@AdjustMode
على الرغم من أنه يمكنك إضافة صلة إلى بيان التحديد الخاص بك ، فقد وجدنا أنه من الأسهل الرجوع إليه في وظيفة أخرى. عند إضافة تاريخين ، يمكن تكرار كل من رقم الأسبوع التصاعدي والتنازلي وعدد التواريخ ذات الصلة للتحقق من المطابقة.
CREATE FUNCTION Dates.DateJoin(@CalendarDate DATETIME,@CrossoverDate DATETIME,@Frequency INT,@WeekAsc INT,@WeekDesc INT) RETURNS BIT AS BEGINDECLARE @J BIT=0/*Once*/IF @CrossoverDate=@CalendarDate SET @J=1/*Weekly/Fornightly*/IF @J=0 AND @Frequency IN (7,14) BEGINIF DATEPART(WEEKDAY,@CrossoverDate)=DATEPART(WEEKDAY,@CalendarDate) AND DATEDIFF(DAY,@CrossoverDate,@CalendarDate)%@Frequency=0SET @J=1END/*Monthly*/IF @J=0 AND @Frequency IN (32) BEGINIF (DATEPART(DAY,@CalendarDate)=DATEPART(DAY,@CrossoverDate)) OR (DATEPART(MONTH,DATEADD(DAY,1,@CalendarDate))<>DATEPART(MONTH,@CalendarDate)ANDDATEPART(MONTH,DATEADD(DAY,1,DATEADD(MONTH,DATEDIFF(MONTH,@CrossoverDate,@CalendarDate),@CrossoverDate)))<>DATEPART(MONTH,DATEADD(MONTH,DATEDIFF(MONTH,@CrossoverDate,@CalendarDate),@CrossoverDate)))SET @J=1END/*First/Second/Third/Fourth*/IF @J=0 AND @Frequency IN (1,2,3,4)IF (DATEPART(WEEKDAY,@CrossoverDate)=DATEPART(WEEKDAY,@CalendarDate) AND @WeekAsc=@Frequency) SET @J=1/*First/Second/Third/Fourth Last*/IF @J=0 AND @Frequency IN (-1,-2,-3,-4)IF (DATEPART(WEEKDAY,@CrossoverDate)=DATEPART(WEEKDAY,@CalendarDate) AND @WeekDesc=(-@Frequency)) SET @J=1RETURN @JENDGO
CREATE FUNCTION Dates.DateJoin(@CalendarDate DATETIME,@CrossoverDate DATETIME,@Frequency INT,@WeekAsc INT,@WeekDesc INT) RETURNS BIT AS BEGIN
DECLARE @J BIT=0
/*Once*/
IF @CrossoverDate=@CalendarDate SET @J=1
/*Weekly/Fornightly*/
IF @J=0 AND @Frequency IN (7,14) BEGIN
IF DATEPART(WEEKDAY,@CrossoverDate)=DATEPART(WEEKDAY,@CalendarDate) AND DATEDIFF(DAY,@CrossoverDate,@CalendarDate)%@Frequency=0
SET @J=1
/*Monthly*/
IF @J=0 AND @Frequency IN (32) BEGIN
IF (DATEPART(DAY,@CalendarDate)=DATEPART(DAY,@CrossoverDate)
) OR (
DATEPART(MONTH,DATEADD(DAY,1,@CalendarDate))<>
DATEPART(MONTH,@CalendarDate)
AND
DATEPART(MONTH,DATEADD(DAY,1,DATEADD(MONTH,DATEDIFF(MONTH,@CrossoverDate,@CalendarDate),@CrossoverDate)))<>
DATEPART(MONTH,DATEADD(MONTH,DATEDIFF(MONTH,@CrossoverDate,@CalendarDate),@CrossoverDate))
)
/*First/Second/Third/Fourth*/
IF @J=0 AND @Frequency IN (1,2,3,4)
IF (DATEPART(WEEKDAY,@CrossoverDate)=DATEPART(WEEKDAY,@CalendarDate) AND @WeekAsc=@Frequency) SET @J=1
/*First/Second/Third/Fourth Last*/
IF @J=0 AND @Frequency IN (-1,-2,-3,-4)
IF (DATEPART(WEEKDAY,@CrossoverDate)=DATEPART(WEEKDAY,@CalendarDate) AND @WeekDesc=(-@Frequency)) SET @J=1
RETURN @J
يمكننا الآن اختبار هذه البيانات عن طريق إنشاء جدول جدولة مزيف.
في مثالنا ، ننشئ تقريرًا للمتابعة في الأول من يناير 2017 ، وكرر كل شهر في الأول مع توفر كل إجراء ممكن للتاريخ.
إذا نظرنا إلى 01/01/2018 بمعزل عن العالم الخارجي ، قد يؤدي سلوك عطلة البنوك إلى تغيير نطاق التاريخ إلى أي شيء من 29/12/2017 إلى 02/01/2018.
CREATE TABLE Test.Schedule(ReportID INT,ReportName NVARCHAR(100),ReportStartDate DATETIME2,ReportFrequency INT,ReportAdjustBehaviour BIT,ReportAdjustWeekend BIT,ReportAdjustHoliday BIT,ProducedBy NVARCHAR(100))GOINSERT INTO Test.Schedule SELECT 1,'Schedule Before - Weekends=0,Holidays=0','2017-01-01',32,1,0,0,''INSERT INTO Test.Schedule SELECT 1,'Schedule Before - Weekends=1,Holidays=0','2017-01-01',32,1,1,0,''INSERT INTO Test.Schedule SELECT 1,'Schedule Before - Weekends=0,Holidays=1','2017-01-01',32,1,0,1,''INSERT INTO Test.Schedule SELECT 1,'Schedule Before - Weekends=1,Holidays=1','2017-01-01',32,1,1,1,''INSERT INTO Test.Schedule SELECT 1,'Schedule After - Weekends=0,Holidays=0','2017-01-01',32,0,0,0,''INSERT INTO Test.Schedule SELECT 1,'Schedule After - Weekends=1,Holidays=0','2017-01-01',32,0,1,0,''INSERT INTO Test.Schedule SELECT 1,'Schedule After - Weekends=0,Holidays=1','2017-01-01',32,0,0,1,''INSERT INTO Test.Schedule SELECT 1,'Schedule After - Weekends=1,Holidays=1','2017-01-01',32,0,1,1,''GOSELECT (case DATEPART(weekday,DateAdjusted) 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) DayAdjusted,DateAdjusted,WeekDayName,CalendarDate,ReportName,FrequencyNameFROM (SELECT Dates.GetDateAdjusted(CalendarDate,0,0,ReportAdjustBehaviour,ReportAdjustWeekend,ReportAdjustHoliday) DateAdjusted,c.CalendarDate,WeekDayName,s.*,f.FrequencyNameFROM (SELECT * FROM Dates.Calendar WHERE CalendarDate BETWEEN '2018-01-01' AND '2018-12-31') cINNER JOIN Test.Schedule s ON (CalendarDate>=ReportStartDate --AND CalendarDate>GETDATE()) AND (Dates.DateJoin(CalendarDate,ReportStartDate,ReportFrequency,CalendarCA,CalendarCD)=1)INNER JOIN Dates.CalendarFrequency f ON f.FrequencyID=ReportFrequency) xORDER BY CalendarDate,DateAdjusted,ReportName
CREATE TABLE Test.Schedule(
ReportID INT,
ReportName NVARCHAR(100),
ReportStartDate DATETIME2,
ReportFrequency INT,
ReportAdjustBehaviour BIT,
ReportAdjustWeekend BIT,
ReportAdjustHoliday BIT,
ProducedBy NVARCHAR(100))
INSERT INTO Test.Schedule SELECT 1,'Schedule Before - Weekends=0,Holidays=0','2017-01-01',32,1,0,0,''
INSERT INTO Test.Schedule SELECT 1,'Schedule Before - Weekends=1,Holidays=0','2017-01-01',32,1,1,0,''
INSERT INTO Test.Schedule SELECT 1,'Schedule Before - Weekends=0,Holidays=1','2017-01-01',32,1,0,1,''
INSERT INTO Test.Schedule SELECT 1,'Schedule Before - Weekends=1,Holidays=1','2017-01-01',32,1,1,1,''
INSERT INTO Test.Schedule SELECT 1,'Schedule After - Weekends=0,Holidays=0','2017-01-01',32,0,0,0,''
INSERT INTO Test.Schedule SELECT 1,'Schedule After - Weekends=1,Holidays=0','2017-01-01',32,0,1,0,''
INSERT INTO Test.Schedule SELECT 1,'Schedule After - Weekends=0,Holidays=1','2017-01-01',32,0,0,1,''
INSERT INTO Test.Schedule SELECT 1,'Schedule After - Weekends=1,Holidays=1','2017-01-01',32,0,1,1,''
SELECT (case DATEPART(weekday,DateAdjusted) 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) DayAdjusted,
DateAdjusted,WeekDayName,CalendarDate,ReportName,FrequencyName
FROM (
SELECT Dates.GetDateAdjusted(CalendarDate,0,0,ReportAdjustBehaviour,ReportAdjustWeekend,ReportAdjustHoliday) DateAdjusted,c.CalendarDate,WeekDayName,s.*,f.FrequencyName
FROM (SELECT * FROM Dates.Calendar WHERE CalendarDate BETWEEN '2018-01-01' AND '2018-12-31') c
INNER JOIN Test.Schedule s ON (CalendarDate>=ReportStartDate --AND CalendarDate>GETDATE()
) AND (Dates.DateJoin(CalendarDate,ReportStartDate,ReportFrequency,CalendarCA,CalendarCD)=1)
INNER JOIN Dates.CalendarFrequency f ON f.FrequencyID=ReportFrequency
) x
ORDER BY CalendarDate,DateAdjusted,ReportName