Pure SQL : générer une série de dates/temps

Submitted by st on

Il existe plusieurs exemples de fonction table qui génère une série de dates (temps) entre les 2 valeurs données. Souvent en décisionnel vous avez une table de séries de dates/temps préalablement remplie afin de ne pas générer les séries à la volée.

Par contre, il n'est pas toujours possible s'appuyer sur la fonction utilisateur ainsi que la table des séries peut manquer. Dans ce cas prenez la méthode "pure SQL" suivante.

Supposons, que nous avons une date de début et celle de fin. Voici le code TSQL qui génère la série des dates "mois par mois".

WITH date_limits (min_date_value, max_date_value) AS
(
  SELECT convert(date, '20100101'),
         convert(date, '20110201')
)
,
date_series (date_value, num) AS
(
  SELECT min_date_value AS date_value, 1
  FROM date_limits
  UNION ALL
  SELECT dateadd(month, 1, date_series.date_value), num + 1
  FROM date_limits INNER JOIN date_series 
         ON date_series.date_value < date_limits.max_date_value
)
SELECT date_value, num
FROM date_series

Le résultat :

date_value num
---------- -----------
2010-01-01 1
2010-02-01 2
2010-03-01 3
2010-04-01 4
2010-05-01 5
2010-06-01 6
2010-07-01 7
2010-08-01 8
2010-09-01 9
2010-10-01 10
2010-11-01 11
2010-12-01 12
2011-01-01 13
2011-02-01 14

Une exemple réelle décisionnel : transposer les valeurs données par ces périodes par mois.

CREATE TABLE #t (
  date_from date,
  date_to date,
  value int
)
GO
INSERT INTO #t
SELECT '20100101', '20100301', 1
UNION
SELECT '20100401', '20100501', 2
UNION
SELECT '20100501', '20100901', 3
GO
SELECT * FROM #t;
 
WITH date_limits (min_date_value, max_date_value) AS
(
  SELECT min(date_from), max(date_to)
  FROM #t
)
,date_series (date_value, num) AS
(
  SELECT min_date_value AS date_value, 1
  FROM date_limits
  UNION ALL
  SELECT dateadd(month, 1, date_series.date_value), num + 1
  FROM date_limits 
       INNER JOIN date_series 
       ON date_series.date_value < date_limits.max_date_value
)
SELECT ds.date_value, t.value
FROM date_series ds 
     INNER JOIN #t t
     ON ds.date_value BETWEEN t.date_from AND t.date_to
GO
 
DROP TABLE #t
GO

Résultats :

date_from  date_to    value
---------- ---------- -----------
2010-01-01 2010-03-01 1
2010-04-01 2010-05-01 2
2010-05-01 2010-09-01 3
 
date_value value
---------- -----------
2010-01-01 1
2010-02-01 1
2010-03-01 1
2010-04-01 2
2010-05-01 2
2010-05-01 3
2010-06-01 3
2010-07-01 3
2010-08-01 3
2010-09-01 3