SQL Server 柔軟な日付関数とカレンダーテーブル

カレンダーテーブルを使用して、銀行の休日と週末を格納および計算し、繰り返しの頻度を設定する、最も移植性の高い関数のいくつかを共有します。

約

私たちがこれまで行ってきた作業の大部分は、日付、年齢、その他のさまざまな情報を扱うことでした。年齢の計算などは、それほど単純ではありません。以前にいくつかの記事を書きましたが、必要な関数の数を減らすために、いくつかのより柔軟な関数に取って代わられています。

この記事では、再利用可能なカレンダーテーブルと銀行休業日テーブルを設定し、データを入力します。さまざまな機能は別の記事として書かれ、詳細情報を得ることができます。

私たちは、変更管理の必要性と一貫したデータを削減するために、すべての機能を備えた共有の「ユーティリティ」データベースを使用する傾向があります。

データベースの作成

共有データベースを持っている場合、またはこれらの関数をどこに保存するかがわかっている場合は、自由に使用できますが、ここからのすべてのコードは、SQL Server 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

スキーマの作成

管理を容易にするために、Dates というスキーマを作成し、関連するすべての情報をこれに保存します。この意味でのスキーマは、関連するオブジェクトのコンテナーまたは名前空間と考えてください。

ヒント！ - バッチ SQL ステートメントでスキーマを作成する場合は、それを sp_executesql ステートメントにラップして、バッチ内の唯一のステートメントであるというシステムの不満を回避します。存在する可能性がある場合は、それを try ブロックでラップして、コードを実行できるようにします。

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

共有機能

さらに使用する共有関数がいくつかあるため、コードとリンクを提供し、各ページで説明します。注意すべき点がいくつかあります。

標準の DateDiff 年の計算は純粋に年対年であるため、年齢を計算するには、その日付がそれより高いかどうかを確認する必要があります。 GetLeapYear 関数は、1 日追加する必要がある場合に最も賢明な方法です。

新しい 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 @RetENDGO

年がうるう年の場合の機能チェックの作成

GetAge

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 @AgeENDGO

年齢を取得

DatePad

CREATE FUNCTION Dates.DatePad(@PadValue NVARCHAR(100),@PadLen INT) RETURNS NVARCHAR(4) AS BEGINRETURN ISNULL(REPLICATE('0',@PadLen-LEN(@PadValue))+@PadValue,LEFT(@PadValue,@PadLen))ENDGO

日付パディング機能

DateFromParts

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 @DateENDGO

2012以前のバージョンのSQL Server関数DATEFROMPARTS

GetEasterDate

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 の範囲内の任意の期間実行するように設定されています。一般的な使用では、以下のコードはほとんどのシナリオをカバーする範囲を提供します。

CalendarCA と CalendarCD は、各月の昇順および降順の週番号であり、上記の度数表との相互参照に使用されます。

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

祝日表

祝日テーブルは可能な限り柔軟に設計されており、Calendar Function 列を追加することでさまざまなオプションを保存できるようになっています。以下では、イングランドとウェールズ、スコットランド、北アイルランドの祝日を別々に保存し、既に作成されたカレンダーテーブルを相互参照しています。

理論的には、好きなだけスキームを持つことができます。

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

賢いビット

さまざまな方法でそれらを組み合わせた 3 つの関数が使用されていますが、すべて非常によく似たオプションがあります。

GetDateAdjusted は、カレンダー機能、および休日や週末を含めるかどうかに応じて、設定された日数だけ日付を前後に移動します。
GetDaysAdjusted は、2 つの日付の間の日数をカウントします。これは、カレンダー関数と、休日や週末を含めるかどうかによって異なります。日付は、包括的または排他的にカウントできます。
GetMonthAdjusted は、カレンダー関数、および休日や週末を含めるかどうかに応じて、1 か月の日数をカウントします。日付は、包括的または排他的にカウントできます。
GetMonthDay は、月の特定の日、最初、2 番目、最後の日などを返します。

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

柔軟なオプションを備えたSQL Server Functionに稼働日を追加する

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))+@AdjustModeENDGO

柔軟なオプションを備えたSQL Server関数の日付間の稼働日

GetMonthAdjusted

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))+@AdjustModeEND

柔軟なオプションを使用してSQL Server関数で月の日数を数える

GetMonthDay

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 @CurDateIF (SELECT (CASE @Type WHEN 1 THEN (CASE WHEN DATEPART(WeekDay,@CurDate)=1 THEN 1 ELSE 0 END)WHEN 2 THEN(CASE WHEN DATEPART(WeekDay,@CurDate)=2 THEN 1 ELSE 0 END)WHEN 3 THEN(CASE WHEN DATEPART(WeekDay,@CurDate)=3 THEN 1 ELSE 0 END)WHEN 4 THEN(CASE WHEN DATEPART(WeekDay,@CurDate)=4 THEN 1 ELSE 0 END)WHEN 5 THEN(CASE WHEN DATEPART(WeekDay,@CurDate)=5 THEN 1 ELSE 0 END)WHEN 6 THEN(CASE WHEN DATEPART(WeekDay,@CurDate)=6 THEN 1 ELSE 0 END)WHEN 7 THEN(CASE WHEN DATEPART(WeekDay,@CurDate)=7 THEN 1 ELSE 0 END)WHEN 8 THEN(CASE WHEN DATEPART(WeekDay,@CurDate) IN (1,2,3,4,5,6,7) THEN 1 ELSE 0 END)WHEN 9 THEN(CASE WHEN DATEPART(WeekDay,@CurDate) IN (2,3,4,5,6) THEN 1 ELSE 0 END)WHEN 10 THEN (CASE WHEN DATEPART(WeekDay,@CurDate) IN (1,7) THEN 1 ELSE 0 END)ELSE 0 END))=1 BEGINSET @Matches = @Matches+1SET @TempDate = @CurDateENDIF @Matches=@Counter AND @Counter<32 BEGINSET @Date=@TempDateENDSET @CurDate=DATEADD(DAY,1,@CurDate)ENDRETURN ISNULL(@Date,@TempDate)ENDGO

特定の日を返す関数

データの結合

選択ステートメントに結合を追加することもできますが、別の関数で参照する方が簡単であることがわかりました。 2 つの日付、頻度、月の昇順および降順の両方の週番号を追加すると、関連する日付が一致するかどうかを確認できます。

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 年 1 月 1 日にレポートを作成し、可能なすべての日付アクションを使用して毎月 1 日に繰り返します。

2018 年 1 月 1 日を単独で見ると、週末と祝日の行動により、日付範囲が 2017 年 12 月 29 日から 2018 年 2 月 1 日までのいずれかに変更される可能性があります。

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,ReportName

Results

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)

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)