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
OPTION (MAXRECURSION n)fixe le nombre maximal de niveaux de récursion. Sans cette option, la valeur par défaut est de 100 niveaux : une plage de dates plus longue échoue.- Les exemples utilisent 32 747, valeur inférieure à la limite maximale de 32 767.