روزها را در یک ماه در یک عملکرد SQL Server با گزینه های قابل انعطاف حساب کنید
یک عملکرد SQL Server قابل انعطاف و قابل استفاده مجدد که می تواند تعداد روزهای یک ماه را با گزینه محرومیت از تعطیلات یا آخر هفته محاسبه کند.
اگر مطالب دیگر را بررسی کرده اید ، می توانید جدول را دراختیار داشته باشید ، در غیر اینصورت از کد زیر برای ایجاد آن استفاده کنید. اینها در تعطیلات استاندارد در انگلیس و ولز است. در صفحه اصلی توابع سایر کشورها وجود دارد.
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 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اکنون داده داریم ، می توانیم تابعی را ایجاد کنیم که هر روز از آغاز تا پایان یک ماه به چند متغیر وابسته باشد:
- Month - در هر تاریخ تصویب و سیستم برای آن ماه محاسبه می کند.
- CalendarFunction - عملکردی که می خواهید از آن استفاده کنید.
- AdjustMode - روزهای اختصاصی یا اختصاصی ، باید 1 به عنوان استاندارد باشد ، اما از 0 استفاده کنید تا یک روز مرخصی را برای استفاده در ترکیب با سایر عملکردها (مانند اضافه کردن روز) استفاده کنید.
- AdjustWeekends - آخر هفته را از محاسبات شما مستثنی نمی کند
- AdjustHolidays - اگر عملکرد تعطیلات مطابقت داشته باشد ، تعطیلات را مستثنی نمی کند
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
SELECT Dates.GetMonthAdjusted('2014-01-01',0,1,1,1) --22
SELECT Dates.GetMonthAdjusted('2014-01-01',0,1,1,0) --23
SELECT Dates.GetMonthAdjusted('2014-01-01',0,1,0,1) --30
SELECT Dates.GetMonthAdjusted('2014-01-01',0,1,0,0) --31
SELECT Dates.GetMonthAdjusted('2014-02-04',0,1,1,1) --20
SELECT Dates.GetMonthAdjusted('2014-05-15',0,1,1,1) --20 (22-2)