LLI Wiki — My Digital Knowledge Base

Générer une table de dates avec une CTE récursive dans SQL Server

Requêtes T-SQL qui génèrent une ligne par jour, par mois ou par semaine pour une dimension temps.

Une expression de table commune (CTE) récursive permet de générer une table de dates, par exemple pour une dimension temps dans un entrepôt de données. Les requêtes ci-dessous produisent une ligne par jour, par mois puis par semaine.

Une ligne par jour

-- A recursive common table expression (CTE) to create
-- a table with dates within a defined range.
-- Maximum count of dates: 32,747 (below the MAXRECURSION limit of 32,767)

DECLARE @startDate datetime, @endDate datetime;

SET @startDate = {d N'2011-01-01'};
SET @endDate   = {d N'2011-12-31'};

WITH [dates] ([Sequence], [date], [year], [month], [day]) AS
   (SELECT 1 AS [Sequence]
          ,@startDate AS [date]
          ,YEAR(@startdate) AS [year]
          ,MONTH(@startdate) AS [month]
          ,DAY(@startdate) AS [day]
    UNION ALL
    SELECT Sequence + 1 AS Sequence
          ,DATEADD(d, 1, [date]) AS [date]
          ,YEAR(DATEADD(d, 1, [date])) AS [year]
          ,MONTH(DATEADD(d, 1, [date])) AS [month]
          ,DAY(DATEADD(d, 1, [date])) AS [day]
    FROM [dates]
    WHERE [date] < @endDate)

SELECT [Sequence]
      ,[date]
      ,[year]
      ,[month]
      ,[day]
FROM [dates]
OPTION (MAXRECURSION 32747);

Une ligne par mois

DECLARE @startDate datetime, @endDate datetime;

SET @startDate = {d N'2011-01-01'};
SET @endDate   = {d N'2011-12-31'};

WITH [dates] ([Sequence], [date], [year], [month]) AS
   (SELECT 1 AS [Sequence]
          ,@startDate AS [date]
          ,YEAR(@startDate) AS [year]
          ,MONTH(@startDate) AS [month]
    UNION ALL
    SELECT Sequence + 1 AS Sequence
          ,DATEADD(m, 1, [date]) AS [date]
          ,YEAR(DATEADD(m, 1, [date])) AS [year]
          ,MONTH(DATEADD(m, 1, [date])) AS [month]
    FROM [dates]
    WHERE [date] < @endDate)

SELECT [Sequence]
      ,[date]
      ,[year]
      ,[month]
FROM [dates]
OPTION (MAXRECURSION 32747);

Une ligne par semaine

DECLARE @startDate DATETIME, @endDate DATETIME;

SET @startDate = {D N'2014-01-01'};
SET @endDate = {D N'2016-07-01'};

WITH [dates] ([Sequence], [date], [year], [month], [week], [day])
          AS (SELECT 1 AS [Sequence],
                     @startDate AS [date],
                     YEAR(@startDate) AS [year],
                     MONTH(@startDate) AS [month],
                     DATEPART(WEEK, @startDate) AS [week],
                     DAY(@startDate) AS [day]
               UNION ALL
               SELECT Sequence + 1 AS Sequence,
                      DATEADD(WEEK, 1, [date]) AS [date],
                      YEAR(DATEADD(WEEK, 1, [date])) AS [year],
                      MONTH(DATEADD(WEEK, 1, [date])) AS [month],
                      DATEPART(WEEK, DATEADD(WEEK, 1, [date])) AS [week],
                      DAY(DATEADD(WEEK, 1, [date])) AS [day]
               FROM [dates]
               WHERE [date] < @endDate)

SELECT * FROM [dates]
OPTION (MAXRECURSION 32747);

Remarques

Mastodon LLIMastodon SocialFosstodon