柔軟なオプションを備えたSQL Server Functionに稼働日を追加する
休日または週末を除外するオプションを使用して日付に日数を追加する、柔軟で再利用可能な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データができたので、開始から終了まで毎日ループし、土曜日、日曜日、休日のデータベースではない@Countに1を追加する関数を作成できます。
以前の機能を柔軟にすることができるというフィードバックがありましたが、いくつかの追加のクライアントのニーズに再訪して、これをより多くのオプションで更新しました：
- @AdjustDate-変更する日付
- @CalenderFunction-使用する休日関数（英国の一部の国では日付が異なるため、異なる関数に保存できます）
- @AdjustDats-基準日から追加または削除する日数
- @AdjustMode-加算日数の場合は0、減算日数の場合は1
- @AdjustWeekend-週末を計算から除外します
- @AdjustHolidays-休日関数が一致する場合、休日を除外します
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
/*Add Days*/
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
/*Subtract Days*/
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
SELECT Dates.GetDateAdjusted('2014-05-01',0,1,0,1,1)--'2014-05-02'
SELECT Dates.GetDateAdjusted('2014-05-01',0,2,0,0,0)--'2014-05-03'
SELECT Dates.GetDateAdjusted('2014-05-01',0,2,0,1,1)--'2014-05-06'
SELECT Dates.GetDateAdjusted('2014-05-01',0,3,0,1,1)--'2014-05-07'
SELECT Dates.GetDateAdjusted('2014-05-01',0,4,0,1,1)--'2014-05-08'
SELECT Dates.GetDateAdjusted('2014-05-01',0,5,0,1,1)--'2014-05-09'
SELECT Dates.GetDateAdjusted('2014-05-01',0,6,0,1,1)--'2014-05-12'