SQL सर्वर लचीला दिनांक कार्य और कैलेंडर तालिका
हम बैंक छुट्टियों और सप्ताहांतों के साथ-साथ आवर्ती आवृत्तियों को सेट करने और गणना करने के लिए कैलेंडर तालिका का उपयोग करके अपने कुछ सबसे पोर्टेबल कार्यों को साझा करते हैं।
के बारे में
हमारे द्वारा पहले किए गए अधिकांश कार्य में दिनांक, आयु और विभिन्न अन्य सूचनाओं के साथ काम करना शामिल है। उम्र की गणना जैसी चीजें उतनी सरल नहीं हैं जितनी हो सकती हैं। हमने पहले लेखों का एक चयन लिखा है, लेकिन अब आपके लिए आवश्यक कार्यों की संख्या को कम करने के लिए इन्हें कुछ अधिक लचीले कार्यों द्वारा प्रतिस्थापित किया जा रहा है।
इस लेख में हम अपनी पुन: प्रयोज्य कैलेंडर तालिका और बैंक अवकाश तालिका सेट करने जा रहे हैं, फिर इसे पॉप्युलेट करें। विभिन्न कार्यों को अलग-अलग लेखों के रूप में लिखा जाएगा जहाँ आप आगे की जानकारी प्राप्त कर सकते हैं।
हम परिवर्तन नियंत्रण आवश्यकताओं और सुसंगत डेटा को कम करने के लिए अपने सभी कार्यों के साथ एक साझा "उपयोगिता" डेटाबेस का उपयोग करते हैं।
डेटाबेस बनाएं
यदि आपके पास एक साझा डेटाबेस है, या आप जानते हैं कि आप इसे इन कार्यों को कहाँ संग्रहीत करना चाहते हैं, तो बेझिझक महसूस करें, हालाँकि यहाँ से सभी कोड SQL सर्वर 2016 में एकल स्क्रिप्ट में चलाने के लिए डिज़ाइन किए गए हैं।
टिप्पणी! - SSMS में अपना डेटाबेस बनाना आसान हो सकता है
बख्शीश! - लॉग फ़ाइल ब्लोट से बचने के लिए विकल्पों को सरल पर सेट करें और डेटा के व्यावसायिक रूप से महत्वपूर्ण होने की संभावना कम है।
SQL
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 स्टेटमेंट में लपेटें ताकि सिस्टम को यह शिकायत न हो कि यह बैच में एकमात्र स्टेटमेंट है। यदि यह मौजूद हो सकता है तो कोड को चलाने की अनुमति देने के लिए इसे एक कोशिश ब्लॉक में लपेटें।
SQL
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साझा कार्य
कुछ साझा कार्य हैं जिनका आगे उपयोग किया जाएगा, इसलिए हम प्रत्येक पृष्ठ पर एक स्पष्टीकरण के साथ कोड और लिंक प्रदान करने जा रहे हैं। ध्यान देने योग्य कुछ बातें हैं:
मानक दिनांकडिफ़ वर्ष की गणना विशुद्ध रूप से वर्ष-वर्ष है, इसलिए आयु की गणना करने के लिए आपको यह जांचना होगा कि वह तिथि अधिक है या नहीं। यदि हमें एक दिन जोड़ने की आवश्यकता है तो हमारा GetLeapYear फ़ंक्शन काम करने का सबसे समझदार तरीका था।
यदि आप डे पैडिंग की कमी के कारण नए DateFromParts फ़ंक्शन का उपयोग नहीं करते हैं, तो ईस्टर तिथि विफल हो सकती है।
GetLeapYear
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 @RetENDGOGetAge
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 @AgeENDGODatePad
CREATE FUNCTION Dates.DatePad(@PadValue NVARCHAR(100),@PadLen INT) RETURNS NVARCHAR(4) AS BEGINRETURN ISNULL(REPLICATE('0',@PadLen-LEN(@PadValue))+@PadValue,LEFT(@PadValue,@PadLen))ENDGODateFromParts
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 @DateENDGOGetEasterDate
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आवृत्ति तालिका
शेड्यूलिंग और पुनरावृत्ति के लिए, हम अपनी शेड्यूलिंग टेबल का उपयोग करते हैं। यह हमें यह गणना करने में सक्षम बनाता है कि किसी चीज़ को कितनी बार दोहराया जाना चाहिए।
Calendar Frequency
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कैलेंडर तालिका
हमारी कैलेंडर तालिका को DATETIME2 सीमा के भीतर किसी भी समय चलने के लिए सेट अप किया गया है। सामान्य उपयोग में, नीचे दिया गया कोड आपको एक श्रेणी देगा जिसमें अधिकांश परिदृश्य शामिल होंगे।
कैलेंडरसीए और कैलेंडरसीडी प्रत्येक महीने के लिए आरोही और अवरोही सप्ताह की संख्या है, और ऊपर हमारी आवृत्ति तालिका के खिलाफ एक क्रॉस संदर्भ में उपयोग किया जाता है।
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))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 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.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चतुर बिट्स
तीन कार्यों का उपयोग किया जाता है जो उन्हें विभिन्न तरीकों से जोड़ते हैं, सभी बहुत समान विकल्पों के साथ।
- GetDateAdjusted कैलेंडर फ़ंक्शन पर निर्भर करता है, और चाहे आप छुट्टियों और या सप्ताहांत को शामिल करना चाहते हैं, दिनों की एक निर्धारित संख्या से तारीख को आगे या पीछे ले जाता है।
- GetDaysAdjusted दो तिथियों के बीच दिनों की संख्या की गणना करता है, कैलेंडर फ़ंक्शन पर निर्भर करता है, और आप छुट्टियों और या सप्ताहांत को शामिल करना चाहते हैं या नहीं। तिथियों की गणना समावेशी या अनन्य रूप से की जा सकती है।
- GetMonthAdjusted कैलेंडर फ़ंक्शन पर निर्भर करता है, और आप छुट्टियों और या सप्ताहांत को शामिल करना चाहते हैं या नहीं, एक महीने में दिनों की संख्या की गणना करता है। तिथियों की गणना समावेशी या अनन्य रूप से की जा सकती है।
- GetMonthDay महीने का एक विशिष्ट दिन देता है, पहला, दूसरा, आखिरी आदि।
GetDateAdjusted
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
GetDaysAdjusted
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))+@AdjustModeENDGOGetMonthAdjusted
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))+@AdjustModeENDGetMonthDay
CREATE FUNCTION dbo.GetMonthDay(@Month DATETIME,@Type INT, @Counter INT) RETURNS DATETIME AS BEGINSET @Month = CONVERT(DATE,DATEADD(DAY,1-DATEPART(DAY,@Month),@Month))DECLARE @CurDate DATETIME=@Month,@Date DATETIME,@Matches INT=0,@TempDate DATETIMEWHILE @CurDateडेटा में शामिल होना
जब आप अपने चयन कथन में शामिल हो सकते हैं, तो हमें किसी अन्य फ़ंक्शन में संदर्भित करना आसान हो गया है। दो तिथियों को जोड़कर, आवृत्ति और आरोही और अवरोही मासिक सप्ताह संख्या दोनों को एक मैच के लिए प्रासंगिक तिथियों की जांच की जा सकती है।
DateJoin Function
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यह सब शामिल हो रहा है - परीक्षण डेटा
अब हम नकली शेड्यूलिंग टेबल बनाकर इस डेटा का परीक्षण कर सकते हैं।
हमारे उदाहरण में पहली जनवरी 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))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,ReportNameResults
| 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) |
