柔軟なオプションを使用して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)