लचीले विकल्पों के साथ SQL सर्वर फ़ंक्शन में दिनांक के बीच कार्य दिवस
एक लचीला और पुन: उपयोग करने योग्य SQL सर्वर फ़ंक्शन जो छुट्टियों या सप्ताहांत को बाहर करने के विकल्प के साथ दो तिथियों के बीच दिनों की संख्या की गणना करेगा
यदि आपने अन्य लेखों की जांच की है तो आपके पास पहले से ही तालिका हो सकती है, यदि नहीं तो इसे बनाने के लिए नीचे दिए गए कोड का उपयोग करें। ये इंग्लैंड और वेल्स में मानक छुट्टियों पर आधारित हैं। मुख्य पृष्ठ पर अन्य देशों के लिए कार्य हैं।
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)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.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 अब हमारे पास डेटा है, हम एक ऐसा फंक्शन बना सकते हैं जो प्रत्येक दिन शुरू से लेकर अंतिम तिथियों तक होता है और नीचे दिए गए प्लग इन पर निर्भर 1 चरों पर निर्भर करता है:
- @DateFrom - अपनी गणना के लिए प्रारंभिक तिथि
- @ कैलेन्डरफंक्शन - अवकाश प्रकार फ़ंक्शन जिसे आप उपयोग करना चाहते हैं
- @DateTo - आप जिस तिथि सीमा का उपयोग करना चाहते हैं उसका अंत
- @ AdjustMode - तारीखों के बीच के दिनों को पहले दिन के विशेष या 1 समावेशी के रूप में परिकलित करें।
- @ AdjustWeekend - आपकी गणना से सप्ताहांत निकाल देता है
- @ AdjustHolidays - छुट्टियों को छोड़ देता है यदि अवकाश फ़ंक्शन मेल खाता है
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))+@AdjustModeENDGOSELECT Dates.GetDaysAdjusted('2014-01-01',0,'2014-01-31',1,1,1)--22SELECT Dates.GetDaysAdjusted('2014-02-01',0,'2014-02-28',1,1,1)--20SELECT Dates.GetDaysAdjusted('2014-05-01',0,'2014-05-02',1,1,1)--2SELECT Dates.GetDaysAdjusted('2014-05-01',0,'2014-05-05',1,1,1)--2SELECT Dates.GetDaysAdjusted('2014-05-01',0,'2014-05-06',1,1,1)--3