باستخدام SQL Server التواريخ

استخدام التواريخ في SQL Server يمكن أن يكون محبطا ويستغرق وقتا طويلا. نحن نشارك بعض من أكثر الوظائف المحمولة لدينا استخدام جدول التقويم لتخزين وحساب العطل الرسمية وعطلات نهاية الأسبوع وكذلك تعيين الترددات المتكررة.

حول

معظم العمل الذي قمنا به سابقًا قد شمل العمل مع التمور والأعمار ومختلف المعلومات الأخرى. أشياء مثل حساب العمر ليست بسيطة كما يمكن أن تكون. لقد كتبنا مجموعة مختارة من المقالات في السابق ، ولكن هذه الحلول تحل محلها الآن وظيفتان أكثر مرونة لتقليل عدد الوظائف التي تحتاجها.

سنقوم في هذه المقالة بإعداد جدول التقويم وإعادة جدول الإجازات المصرفية ، ثم ملءه. سيتم كتابة وظائف مختلفة كمقالات منفصلة حيث يمكنك الحصول على مزيد من المعلومات.

إننا نميل إلى استخدام قاعدة بيانات "Utility" مشتركة مع جميع وظائفنا فيها لتقليل احتياجات التحكم في التغيير والبيانات الثابتة.

إنشاء قاعدة بيانات

إذا كان لديك قاعدة بيانات مشتركة ، أو تعرف أين تريد تخزينها ، فستكون هذه الوظائف خالية ، ولكن تم تصميم كافة التعليمات البرمجية من هنا للتشغيل في برنامج نصي واحد في SQL Server 2016.

ملحوظة! - قد يكون من الأسهل إنشاء قاعدة البيانات الخاصة بك في SSMS

تلميح! - قم بتعيين الخيارات البسيطة لتفادي سخام ملف السجل والبيانات أقل احتمالا للعمل.

SQL

CREATE DATABASE [Utilities]
    CONTAINMENT = NONE ONPRIMARY (
    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

إنشاء مخطط

لتسهيل عملية الإدارة ، قمنا بإنشاء مخطط يسمى Dates ، وقم بتخزين كافة المعلومات ذات الصلة الخاصة بنا في هذا. فكر في المخطط بهذا المعنى على هيئة حاوية أو مساحة اسم للكائنات ذات الصلة.

تلميح! - عند إنشاء مخطط في عبارات SQL للدُفعة ، قم بتغليفه في عبارة sp_executesql لتجنب تشكو النظام من كونه العبارة الوحيدة في الدفعة. إذا كان موجودًا ، فقم بلفه في كتلة المحاولة للسماح بتشغيل الكود.

SQL

USE Utilities
GO
SET NOCOUNT ON;
BEGIN TRY
EXEC sp_executesql N'CREATE SCHEMA Dates'
END TRY BEGIN CATCH END CATCH;
GO
BEGIN TRY
EXEC sp_executesql N'CREATE SCHEMA Test'
END TRY BEGIN CATCH END CATCH;
GO

الوظائف المشتركة

هناك نوعان من الوظائف المشتركة التي سيتم استخدامها بشكل إضافي ، لذلك سنقوم بتوفير الشفرة والرابط ، مع شرح لكل صفحة. هناك أمران يجب ملاحظتهما:

حسابات سنة DateDiff القياسية هي سنة سنة بحتة ، لذلك لحساب العمر تحتاج إلى التحقق مما إذا كان هذا التاريخ أعلى أم لا. كانت وظيفة GetLeapYear لدينا هي الطريقة الأكثر منطقية للعمل إذا كنا نحتاج إلى إضافة يوم.

يمكن أن يفشل تاريخ عيد الفصح إذا لم تستخدم الدالة DateFromParts الجديدة بسبب نقص مساحة اليوم.

GetLeapYear

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
GO

أكثر من: الحصول على سنة كبيسة

GetAge

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(@Date)=1 AND DATEPART(DAYOFYEAR,@Date)>59 THEN 1 ELSE 0 END))
THEN -1 ELSE 0 END)
RETURN @Age
END
GO

أكثر من: احصل على العمر

DatePad

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))
END
GO

أكثر من: تاريخ الحشو وظيفة

DateFromParts

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
END
GO

أكثر من: دالة SQL Server DATEFROMPARTS لإصدارات 2012 السابقة

GetEasterDate

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


END

GO

جدول التردد

للجدولة والتكرار ، نستخدم جدول الجدولة الخاص بنا. هذا يمكننا من حساب عدد المرات التي يجب تكرارها.

Calendar Frequency

CREATE TABLE Dates.CalendarFrequency(FrequencyID NVARCHAR(2),FrequencyName NVARCHAR(100),FrequencyOrder INT)

GO

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

GO

جدول التقويم

تم إعداد جدول التقويم بحيث يتم تشغيله لأي فترة زمنية داخل نطاق DATETIME2. في الاستخدام العام ، سيعطيك الرمز أدناه نطاقًا يغطي معظم السيناريوهات.

CalendarCA و CalendarCD هي أرقام الأسبوع تصاعديًا وتنازليًا لكل شهر ، ويتم استخدامها في مرجع مقابل جدول التردد أعلاه.

Create Calendar Table

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 Holidays Table


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

/*Scotish  Holidays*/

INSERT INTO Dates.CalendarHolidays

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

GO

/*Northern Irish Holidays*/

INSERT INTO Dates.CalendarHolidays

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
GO

ذكي بت

هناك ثلاث وظائف مستخدمة تجمعها بطرق مختلفة ، وكلها بخيارات متشابهة جدًا.

يقوم GetDateAdjusted بنقل التاريخ للأمام أو للخلف بواسطة عدد محدد من الأيام ، يعتمد على وظيفة التقويم ، وما إذا كنت تريد تضمين أيام العطل أو عطلات نهاية الأسبوع.
يقوم GetDaysAdjusted بحساب عدد الأيام بين تاريخين ، تعتمد على دالة التقويم ، وما إذا كنت تريد تضمين أيام العطل و / أو عطلات نهاية الأسبوع. يمكن حساب التواريخ بشكل شامل أو حصري.
تحسب GetMonthAdjusted عدد الأيام في الشهر ، وتعتمد على وظيفة التقويم ، وما إذا كنت تريد تضمين أيام العطل أو عطلات نهاية الأسبوع. يمكن حساب التواريخ بشكل شامل أو حصري.

GetDateAdjusted

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

/*Forwards*/

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

/*Backwards*/

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

أكثر من: التواريخ - إضافة أيام عمل إلى تاريخ باستثناء أيام العطل

GetDaysAdjusted

CREATE FUNCTION Dates.GetDaysAdjusted(@DateFrom As 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

END

DECLARE @Count AS INT=0,@Date As DATETIME2=@DateFrom

WHILE @Date < @DateTo 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

SELECT @Date=DATEADD(DAY,1,@Date)

END

RETURN (DATEDIFF(DAY,@DateFrom,@DateTo)-(@Count))+@AdjustMode

END

GO

أكثر من: حساب أيام العمل بين تاريخين باستثناء أيام العطل في SQL Server

GetMonthAdjusted

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

END

أكثر من: التواريخ - حساب أيام العمل في شهر باستثناء أيام العطل

الانضمام إلى البيانات

على الرغم من أنه يمكنك إضافة ارتباط إلى كشف التحديد الخاص بك ، فقد وجدنا أنه من الأسهل الرجوع إليه في وظيفة أخرى. إضافة تاريخين ، وتكرار ورقم أسبوعي تصاعدي ونزول شهري يمكن التحقق من التواريخ ذات الصلة لمطابقة.

DateJoin Function


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

END

/*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)))

SET @J=1

END

/*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

END

GO

تاريخ الاختبار

يمكننا الآن اختبار هذه البيانات من خلال إنشاء جدول جدولة زائف.

في مثالنا إنشاء تقرير للذهاب في الأول من يناير 2017 ، وتكرار كل شهر على الأول مع كل إجراء تاريخ ممكن المتاحة.

وبالنظر إلى 01/01/2018 في سلوك العزلة وعطلة نهاية الأسبوع والعطلات المصرفية ، يمكن تغيير النطاق الزمني إلى أي شيء من 29/12/2017 إلى 02/01/2018.

Test Data Script


CREATE TABLE Test.Schedule(

ReportID INT,

ReportName NVARCHAR(100),

ReportStartDate DATETIME2,

ReportFrequency INT,

ReportAdjustBehaviour BIT,

ReportAdjustWeekend BIT,

ReportAdjustHoliday BIT,

ProducedBy NVARCHAR(100))

GO

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,''

GO

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

Results

DayAdjusted DateAdjusted WeekDayName CalenderDate ReportName FrequencyName Friday 29/12/2017 Monday 01/01/2018 Schedule Before - Weekends=1,Holidays=1 Monthly (Same Day 1st,15th,28th etc) Sunday 31/12/2017 Monday 01/01/2018 Schedule Before - Weekends=0,Holidays=1 Monthly (Same Day 1st,15th,28th etc) Monday 01/01/2018 Monday 01/01/2018 Schedule After - Weekends=0,Holidays=0 Monthly (Same Day 1st,15th,28th etc) Monday 01/01/2018 Monday 01/01/2018 Schedule After - Weekends=1,Holidays=0 Monthly (Same Day 1st,15th,28th etc) Monday 01/01/2018 Monday 01/01/2018 Schedule Before - Weekends=0,Holidays=0 Monthly (Same Day 1st,15th,28th etc) Monday 01/01/2018 Monday 01/01/2018 Schedule Before - Weekends=1,Holidays=0 Monthly (Same Day 1st,15th,28th etc) Tuesday 02/01/2018 Monday 01/01/2018 Schedule After - Weekends=0,Holidays=1 Monthly (Same Day 1st,15th,28th etc) Tuesday 02/01/2018 Monday 01/01/2018 Schedule After - Weekends=1,Holidays=1 Monthly (Same Day 1st,15th,28th etc)

DayAdjusted	DateAdjusted	WeekDayName	CalenderDate	ReportName	FrequencyName
Friday	29/12/2017	Monday	01/01/2018	Schedule Before - Weekends=1,Holidays=1	Monthly (Same Day 1st,15th,28th etc)
Sunday	31/12/2017	Monday	01/01/2018	Schedule Before - Weekends=0,Holidays=1	Monthly (Same Day 1st,15th,28th etc)
Monday	01/01/2018	Monday	01/01/2018	Schedule After - Weekends=0,Holidays=0	Monthly (Same Day 1st,15th,28th etc)
Monday	01/01/2018	Monday	01/01/2018	Schedule After - Weekends=1,Holidays=0	Monthly (Same Day 1st,15th,28th etc)
Monday	01/01/2018	Monday	01/01/2018	Schedule Before - Weekends=0,Holidays=0	Monthly (Same Day 1st,15th,28th etc)
Monday	01/01/2018	Monday	01/01/2018	Schedule Before - Weekends=1,Holidays=0	Monthly (Same Day 1st,15th,28th etc)
Tuesday	02/01/2018	Monday	01/01/2018	Schedule After - Weekends=0,Holidays=1	Monthly (Same Day 1st,15th,28th etc)
Tuesday	02/01/2018	Monday	01/01/2018	Schedule After - Weekends=1,Holidays=1	Monthly (Same Day 1st,15th,28th etc)