SQL Server でカレンダーテーブルを作る方法

SQL Server（Azure SQL Database）で、カレンダーテーブルが欲しかったので作成方法メモです。

ググったところ、「WITH」でテーブルを作成し、そのテーブル定義の中の「UNION ALL」から再帰呼び出しして作るようです。

WITH

_calendar AS
(
SELECT
    CAST(N'2019-01-01' AS DATE) AS dt
UNION ALL
SELECT
    DATEADD(DAY, 1, _tbl.dt) AS dt  /* 日付間隔はここで調整 */
FROM _calendar AS _tbl
WHERE
    _tbl.dt < N'2019-12-31'
)

SELECT
*
FROM _calendar
OPTION ( MAXRECURSION 0 )  /* 再帰無制限 */

最初のSELECTでスタートして、UNION ALLで連結していくようなイメージです。

UNION ALLの下のFROMには、前のSELECTの結果の１レコードが入り、WHEREの条件チェックが入ります。

条件を満たしていれば、SELECTの計算をして１レコードが作られ、そのレコードが、次のUNION ALLのFROMへと再帰的に続いていきます。

そして、WHEREの条件に一致しなくなると終了します。

再帰回数はデフォルトで100回までのため、オプションで0にして無制限にしています。

これなら連番も作れますね。

/* 10〜1000の10飛び番号 */
WITH

_seq AS
(
SELECT
  10 AS num
UNION ALL
SELECT
  _tbl.num + 10 AS num
FROM _seq AS _tbl
WHERE _tbl.num<=1000-10
)

SELECT
*
FROM _seq
OPTION ( MAXRECURSION 0 )

実験

でも、再帰が実際にどう呼び出されているのか、なんかモヤモヤしたので、スタートに['2019-01-01', '2019-02-01', '2019-03-01']の３レコードを入れて実験

WITH

tbl AS
(
    (
        SELECT
            _a.v
        FROM (VALUES
            (CAST(N'2019-01-01' AS DATE)),
            (CAST(N'2019-02-01' AS DATE)),
            (CAST(N'2019-03-01' AS DATE))
            ) AS _a(v)
    )
    UNION ALL
    (
        SELECT
            DATEADD(DAY, 1, _b.v) AS v
        FROM tbl AS _b
        WHERE DAY(_b.v) <= 2
    )
)

SELECT * FROM tbl