祝日を含むカレンダーデータを作成する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 |
１：日付 ２：祝日名 | 該当月の何週目かを表示 | 日曜日 | 月曜日 | 火曜日 | 水曜日 | 木曜日 | 金曜日 | 土曜日 |
ご不明な点がありましたら
下記の方にコメントを残して頂ければ返信致します。