[SQL Server]CTE式の再帰呼び出しを利用して日付テーブルを作成する

[スポンサードリンク]

「2017/12/28」から「2017/01/05」までとかの日付が格納されたテーブルを作る。あと「2017/05」とかの年月テーブルも作る方法。

概要

たいていのシステムでは、カレンダーテーブルや、稼動日カレンダーなどの日付が格納されたテーブルがあり、それをクロスジョインすれば、ある程度の結果は取れる。
しかし、そんなものがない場合、はクエリで日付テーブルを作成しなければならない。

「WHILE」でループして開始日～終了日を作ると、パフォーマンスが悪くなるため、次のようにする。

年月日テーブルを作成

年月日のみ

クエリで列名を指定した場合

DECLARE @DateFrom varchar(10) = '2016/12/28'
DECLARE @DateTo varchar(10) = '2017/01/05'
;
WITH date_table AS (
    --2016/12/28から2017/01/06までの連番を作成
    SELECT
        @DateFrom AS date_value
    UNION ALL
    SELECT
        CAST(FORMAT(DATEADD(DD,1,CAST(date_value as datetime)),'yyyy/MM/dd') as varchar(10))
    FROM date_table
    WHERE date_value < @DateTo
)
SELECT * FROM date_table

DECLARE @DateFrom varchar(10) = '2016/12/28'

DECLARE @DateTo varchar(10) = '2017/01/05'

;

WITH date_table AS (

--2016/12/28から2017/01/06までの連番を作成

SELECT

@DateFrom AS date_value

UNION ALL

SELECT

CAST(FORMAT(DATEADD(DD,1,CAST(date_value as datetime)),'yyyy/MM/dd') as varchar(10))

FROM date_table

WHERE date_value < @DateTo

)

SELECT * FROM date_table

WITH句で列名を指定した場合

DECLARE @DateFrom varchar(10) = '2016/12/28'
DECLARE @DateTo varchar(10) = '2017/01/05'
;
WITH date_table(date_value) AS (
    --2016/12/28から2017/01/06までの連番を作成
    SELECT
        @DateFrom
    UNION ALL
    SELECT
        CAST(FORMAT(DATEADD(DD,1,CAST(date_value as datetime)),'yyyy/MM/dd') as varchar(10))
    FROM date_table
    WHERE date_value < @DateTo
)
SELECT * FROM date_table

DECLARE @DateFrom varchar(10) = '2016/12/28'

DECLARE @DateTo varchar(10) = '2017/01/05'

;

WITH date_table(date_value) AS (

--2016/12/28から2017/01/06までの連番を作成

SELECT

@DateFrom

UNION ALL

SELECT

CAST(FORMAT(DATEADD(DD,1,CAST(date_value as datetime)),'yyyy/MM/dd') as varchar(10))

FROM date_table

WHERE date_value < @DateTo

)

SELECT * FROM date_table

日付の日本語表記と曜日

DECLARE @DateFrom varchar(10) = '2016/12/28'
DECLARE @DateTo varchar(10) = '2017/01/05'
;
WITH date_table(date_value,disp_date,youbi_value,youbi_name,youbi_small) AS (
    --2016/12/28から2017/01/06までの連番を作成
    SELECT
        @DateFrom,
        CAST(FORMAT(CAST(@DateFrom as datetime),'yyyy年MM月dd日') as varchar(14)),
        DATEPART(WEEKDAY,@DateFrom),
        DATENAME(WEEKDAY,@DateFrom),
        LEFT(DATENAME(WEEKDAY,@DateFrom),1)
    UNION ALL
    SELECT
        CAST(FORMAT(DATEADD(DD,1,CAST(date_value as datetime)),'yyyy/MM/dd') as varchar(10)),
        CAST(FORMAT(DATEADD(DD,1,CAST(date_value as datetime)),'yyyy年MM月dd日') as varchar(14)),
        DATEPART(WEEKDAY,DATEADD(DD,1,CAST(date_value as datetime))),
        DATENAME(WEEKDAY,DATEADD(DD,1,CAST(date_value as datetime))),
        LEFT(DATENAME(WEEKDAY,DATEADD(DD,1,CAST(date_value as datetime))),1)
    FROM date_table
    WHERE date_value < @DateTo
)
SELECT * FROM date_table

DECLARE @DateFrom varchar(10) = '2016/12/28'

DECLARE @DateTo varchar(10) = '2017/01/05'

;

WITH date_table(date_value,disp_date,youbi_value,youbi_name,youbi_small) AS (

--2016/12/28から2017/01/06までの連番を作成

SELECT

@DateFrom,

CAST(FORMAT(CAST(@DateFrom as datetime),'yyyy年MM月dd日') as varchar(14)),

DATEPART(WEEKDAY,@DateFrom),

DATENAME(WEEKDAY,@DateFrom),

LEFT(DATENAME(WEEKDAY,@DateFrom),1)

UNION ALL

SELECT

CAST(FORMAT(DATEADD(DD,1,CAST(date_value as datetime)),'yyyy/MM/dd') as varchar(10)),

CAST(FORMAT(DATEADD(DD,1,CAST(date_value as datetime)),'yyyy年MM月dd日') as varchar(14)),

DATEPART(WEEKDAY,DATEADD(DD,1,CAST(date_value as datetime))),

DATENAME(WEEKDAY,DATEADD(DD,1,CAST(date_value as datetime))),

LEFT(DATENAME(WEEKDAY,DATEADD(DD,1,CAST(date_value as datetime))),1)

FROM date_table

WHERE date_value < @DateTo

)

SELECT * FROM date_table

曜日番号を取得するには「DATEPART関数」、曜日名を取得するには「DATENAME」を使用する。

だいたい
曜日は、プログラム内で処理する場合は曜日番号。表示する際には「○曜日」とか「月、火、水・・・」などの１文字となる。

年月テーブルの作成

スラッシュ区切りのみ

DECLARE @MonthFrom varchar(7) = '2016/12'
DECLARE @MonthTo varchar(7) = '2017/05'
;
WITH month_table(month_value) AS (
    --2016/12から2017/05までの連番を作成
    SELECT
        @MonthFrom
    UNION ALL
    SELECT
        CAST(FORMAT(DATEADD(MM,1,CAST(month_value + '/01' as datetime)),'yyyy/MM') as varchar(7))
    FROM month_table
    WHERE month_value < @MonthTo
)
SELECT * FROM month_table

DECLARE @MonthFrom varchar(7) = '2016/12'

DECLARE @MonthTo varchar(7) = '2017/05'

;

WITH month_table(month_value) AS (

--2016/12から2017/05までの連番を作成

SELECT

@MonthFrom

UNION ALL

SELECT

CAST(FORMAT(DATEADD(MM,1,CAST(month_value + '/01' as datetime)),'yyyy/MM') as varchar(7))

FROM month_table

WHERE month_value < @MonthTo

)

SELECT * FROM month_table

年月表示の場合

DECLARE @MonthFrom varchar(7) = '2016/12'
DECLARE @MonthTo varchar(7) = '2017/05'
;
WITH month_table(month_value,disp_value) AS (
    --2016/12から2017/05までの連番を作成
    SELECT
        @MonthFrom,
        CAST(FORMAT(CAST(@MonthFrom + '/01' as datetime),'yyyy年MM月') as varchar(10))
    UNION ALL
    SELECT
        CAST(FORMAT(DATEADD(MM,1,CAST(month_value + '/01' as datetime)),'yyyy/MM') as varchar(7)),
        CAST(FORMAT(DATEADD(MM,1,CAST(month_value + '/01' as datetime)),'yyyy年MM月') as varchar(10))
    FROM month_table
    WHERE month_value < @MonthTo
)
SELECT * FROM month_table

DECLARE @MonthFrom varchar(7) = '2016/12'

DECLARE @MonthTo varchar(7) = '2017/05'

;

WITH month_table(month_value,disp_value) AS (

--2016/12から2017/05までの連番を作成

SELECT

@MonthFrom,

CAST(FORMAT(CAST(@MonthFrom + '/01' as datetime),'yyyy年MM月') as varchar(10))

UNION ALL

SELECT

CAST(FORMAT(DATEADD(MM,1,CAST(month_value + '/01' as datetime)),'yyyy/MM') as varchar(7)),

CAST(FORMAT(DATEADD(MM,1,CAST(month_value + '/01' as datetime)),'yyyy年MM月') as varchar(10))

FROM month_table

WHERE month_value < @MonthTo

)

SELECT * FROM month_table

注意点

桁数を合わせること

「UNION ALL」の上下でデータ型と桁数を合わせないとエラーになる。

メッセージ 240、レベル 16、状態 1、行 4
再帰クエリ “date_table” の列 “date_value” で、アンカーの型と再帰部分の型が一致していません。

FORMAT関数の戻り値は文字型の最大桁数のため、「varchar(10)」で合わせている。

WHERE句の「＜」について

WHERE句の「＜」を「＜＝」にしなければならないと思いがちだが、それだと値が一つ増えるので「＜」にしておくこと。

[スポンサードリンク]

IT☆ぱらだいす

全てのITエンジニアの役に立ちたい