[SQL Server] 日本カレンダーデータを作成するSQL (祝日を含む) / Japanese Calendar SQL (Holiday)

inno
2013-11-27 11:58 7901 0
祝日を含むカレンダーデータを作成するSQLです。 / Japanese Calendar SQL (Holiday)

 カレンダー作成SQLダウンロード 

ファイル名:CreateCalendarJP.zip

添付のZIPファイルの中には「CreateCalendarJP.sql」ファイルがあります。

そのSQLをそのまま実行すると下記のテーブル及びビューが作成されます。
SQLの実行時間はサーバーは仕様によって多少差はあると思いますが、
私の場合は15秒ぐらいで実行完了しました。

dbo.tComDay : 日付のデータ
dbo.tHoliday : 日本の祝日のデータ
dbo.vCalendar : 「dbo.tComDay」と「dbo.tHoliday」を合わせたビュー

そして各テーブルにデータを登録するSQLも含まれています。
実行完了後、各テーブルのデータを確認すると下記のようなデータが登録されます。

dbo.tComDay」テーブルのデータ


「dbo.tHoliday」テーブルのデータ


dbo.vCalendarビューのデータ


「dbo.vCalendar」ビューのカラムについて説明

 列名

データ型 

説明 

 ComDay

date 

 日付

 WeekdayName

nchar(1)

 曜日

 IsHoliday

bit

 0 : 平日, 1 : 休日、祝日

 HolidayName

nvarchar(20) 

 祝日名


2013年9月のデータを確認する時は下記のSQLを利用します。

SELECT * FROM dbo.vCalendar WITH (NOLOCK)
WHERE ComDay >= '2013-09-01' AND ComDay < '2013-10-01'


カレンダーを作成するSQL
DECLARE @TempMonth char(7)
SET @TempMonth = '2013-05'

DECLARE @TempStartDate char(10), @TempEndDate char(10), @TempWeekStartDate char(10)
SET @TempStartDate = @TempMonth+'-01'
SET @TempEndDate = CONVERT(CHAR(10), DATEADD(MM,1,@TempStartDate), 120)
SET @TempWeekStartDate = CONVERT(CHAR(10),DATEADD(DD, ((DATEPART(DW,@TempStartDate))-1)*(-1), @TempStartDate), 120)

SELECT
Weekend, MAX(D1) D1, MAX(D2) D2, MAX(D3) D3, MAX(D4) D4, MAX(D5) D5, MAX(D6) D6, MAX(D7) D7 
FROM
(
SELECT (DATEDIFF(dd, @TempWeekStartDate ,ComDay)/7)+1 Weekend
, CASE WHEN DATEPART(DW,ComDay) = 1 THEN CONVERT(CHAR(10), ComDay, 120) ELSE NULL END D1
, CASE WHEN DATEPART(DW,ComDay) = 2 THEN CONVERT(CHAR(10), ComDay, 120) ELSE NULL END D2
, CASE WHEN DATEPART(DW,ComDay) = 3 THEN CONVERT(CHAR(10), ComDay, 120) ELSE NULL END D3
, CASE WHEN DATEPART(DW,ComDay) = 4 THEN CONVERT(CHAR(10), ComDay, 120) ELSE NULL END D4
, CASE WHEN DATEPART(DW,ComDay) = 5 THEN CONVERT(CHAR(10), ComDay, 120) ELSE NULL END D5
, CASE WHEN DATEPART(DW,ComDay) = 6 THEN CONVERT(CHAR(10), ComDay, 120) ELSE NULL END D6
, CASE WHEN DATEPART(DW,ComDay) = 7 THEN CONVERT(CHAR(10), ComDay, 120) ELSE NULL END D7
FROM dbo.tComDay WHERE ComDay >= @TempStartDate AND ComDay < @TempEndDate
) A
GROUP BY Weekend
ORDER BY Weekend

実行結果


列の説明

 Weekend

D1 

D2 

D3 

D4 

D5 

D6 

D7 

 該当月の何週目かを表示

日曜日 

曜日 

曜日 

曜日 

曜日 

曜日 

曜日 



祝日を表示するカレンダーを作成するSQL
DECLARE @TempMonth char(7)
SET @TempMonth = '2013-05'

DECLARE @TempStartDate char(10), @TempEndDate char(10), @TempWeekStartDate char(10)
SET @TempStartDate = @TempMonth+'-01'
SET @TempEndDate = CONVERT(CHAR(10), DATEADD(MM,1,@TempStartDate), 120)
SET @TempWeekStartDate = CONVERT(CHAR(10),DATEADD(DD, ((DATEPART(DW,@TempStartDate))-1)*(-1), @TempStartDate), 120)

SELECT
Code, Weekend, MAX(D1) D1, MAX(D2) D2, MAX(D3) D3, MAX(D4) D4, MAX(D5) D5, MAX(D6) D6, MAX(D7) D7 
FROM
(
SELECT B.Code, (DATEDIFF(dd, @TempWeekStartDate ,ComDay)/7)+1 Weekend
, CASE WHEN DATEPART(DW,ComDay) = 1 THEN CASE WHEN Code = 1 THEN CONVERT(NVARCHAR(20), ComDay, 120) ELSE HolidayName END ELSE NULL END D1
, CASE WHEN DATEPART(DW,ComDay) = 2 THEN CASE WHEN Code = 1 THEN CONVERT(NVARCHAR(20), ComDay, 120) ELSE HolidayName END ELSE NULL END D2
, CASE WHEN DATEPART(DW,ComDay) = 3 THEN CASE WHEN Code = 1 THEN CONVERT(NVARCHAR(20), ComDay, 120) ELSE HolidayName END ELSE NULL END D3
, CASE WHEN DATEPART(DW,ComDay) = 4 THEN CASE WHEN Code = 1 THEN CONVERT(NVARCHAR(20), ComDay, 120) ELSE HolidayName END ELSE NULL END D4
, CASE WHEN DATEPART(DW,ComDay) = 5 THEN CASE WHEN Code = 1 THEN CONVERT(NVARCHAR(20), ComDay, 120) ELSE HolidayName END ELSE NULL END D5
, CASE WHEN DATEPART(DW,ComDay) = 6 THEN CASE WHEN Code = 1 THEN CONVERT(NVARCHAR(20), ComDay, 120) ELSE HolidayName END ELSE NULL END D6
, CASE WHEN DATEPART(DW,ComDay) = 7 THEN CASE WHEN Code = 1 THEN CONVERT(NVARCHAR(20), ComDay, 120) ELSE HolidayName END ELSE NULL END D7
FROM dbo.vCalendar A,
(
SELECT 1 Code UNION ALL
SELECT 2 Code 
) B
WHERE ComDay >= @TempStartDate AND ComDay < @TempEndDate
) A 
GROUP BY Code, Weekend
ORDER BY Weekend, Code

実行結果


列の説明

 Code

 Weekend

D1 

D2 

D3 

D4 

D5 

D6 

D7 

1:日付

2:祝日名

 該当月の何週目かを表示

日曜日 

曜日 

曜日 

曜日 

曜日 

曜日 

曜日 


ご不明な点がありましたら
下記の方にコメントを残して頂ければ返信致します。

コメント