Créer une table de dates

Publié le par

Pourquoi ne pas créer une table de dates pour se faciliter les recherches dans des tables suivant la colonne de type date qu’elles contiennent ?
On peut vous demander par exemple de rechercher la quantité d’eau consommée par un parc de machines pour la deuxième semaine de chaque mois d’une année.
Imaginez un peu la complexité de la requête à écrire …
Voyons comment on peut résoudre cette demande très facilement en créant une table de dates …

Voici donc notre table :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
---------------------------------
-- Nicolas SOUQUET - 10/07/2009 -
---------------------------------
CREATE TABLE TbDates
(
  date DATETIME NOT NULL CONSTRAINT PK_TbDates_date PRIMARY KEY,
  annee AS (YEAR(date)) PERSISTED NOT NULL,
  mois AS(MONTH(date)) PERSISTED NOT NULL,
  semaineDansAnnee AS(DATEPART(week, date)) PERSISTED NOT NULL,
  semaineDansMois AS(DATEPART(week, date) - DATEPART(week, DATEADD(day, -DATEPART(day, date) + 1, date)) + 1)  PERSISTED NOT NULL,
  jourDansAnnee AS (DATEPART(dayofyear, date)) PERSISTED NOT NULL,
  jourDansMois AS(DAY(date)) PERSISTED NOT NULL,
  jourDansSemaine TINYINT NULL CONSTRAINT CHK_TbDates_jourDansSemaine CHECK(jourDansSemaine BETWEEN 0 AND 7),
)
GO

Comme toutes les colonnes sont calculées à l’exception de jourDansSemaine (parce que la fonction DATEPART(weekday, date) n’est pas déterministe, elle ne peut pas être utilisée comme spécification d’une colonne calculée), nous n’aurons donc qu’à insérer les dates dans la colonne date.

Comment allons-nous valuer la colonne jourDansSemaine ? Avec le trigger suivant :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
---------------------------------------------------------------------------------
-- Nicolas SOUQUET - 10/07/2009 - Déclencheur qui calcule le jour de la semaine -
--                                pour la table dbo.TbDates                     -
---------------------------------------------------------------------------------
CREATE TRIGGER TR_A_I_TbDates
  ON dbo.TbDates
  AFTER INSERT
AS
BEGIN
  SET NOCOUNT ON
 
  UPDATE dbo.TbDates
  SET jourDansSemaine = DATEPART(weekday, D.date)
  FROM dbo.TbDates AS D
  JOIN INSERTED AS I
    ON D.date = I.date
END
GO

Et pour stocker les dates, il suffira d’exécuter la procédure stockée suivante :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-----------------------------------------------------------------------------------------
-- Nicolas SOUQUET - 10/07/2009 - Procédure de génération de tous les jours d'une année -
-----------------------------------------------------------------------------------------
ALTER PROCEDURE Ps_GenereDatesAnnee
  @annee SMALLINT
AS
BEGIN
  SET NOCOUNT ON
 
  DECLARE @dateDeb DATETIME,
      @dateFin DATETIME
 
  SELECT @dateDeb = CAST(CAST(@annee AS VARCHAR) + '0101' AS DATETIME),
      @dateFin = CAST(CAST(@annee AS VARCHAR) + '1231' AS DATETIME)
 
  ;WITH
    CTE_DATES AS
    (
        SELECT @dateDeb AS Date
      UNION ALL
        SELECT Date + 1
        FROM CTE_DATES
        WHERE Date <= @dateFin
    )
  SELECT Date
  FROM CTE_DATES
  WHERE Date <= @dateFin
  OPTION (MAXRECURSION 366)
END

Ainsi pour stocker tous les jours de l’année 2009, il suffira d’exécuter :

1
2
3
4
5
6
7
8
9
10
11
12
---------------------------------
-- Nicolas SOUQUET - 10/07/2009 -  
---------------------------------
ALTER TABLE dbo.TbDates
ALTER COLUMN jourDansSemaine TINYINT NULL
GO
INSERT INTO dbo.TbDates (date)  
EXEC dbo.Ps_GenereDatesAnnee 2009
GO
ALTER TABLE dbo.TbDates
ALTER COLUMN jourDansSemaine TINYINT NOT NULL
GO

Pour voir ce que contient la table après cette insertion :

1
2
3
4
5
---------------------------------
-- Nicolas SOUQUET - 10/07/2009 -  
---------------------------------
SELECT date, annee, mois, semaineDansAnnee, semaineDansMois, jourDansAnnee, jourDansMois, jourDansSemaine
FROM dbo.TbDates

Finalement, pour rechercher la quantité d’eau consommée par un parc de machines pour la deuxième semaine de chaque mois de l’année 2009, nous devrons écrire :

1
2
3
4
5
6
7
8
9
---------------------------------
-- Nicolas SOUQUET - 10/07/2009 -  
---------------------------------
SELECT SUM(T.conso) AS consoDeuxiemeSemaine2009
FROM dbo.maTable AS T
JOIN dbo.TbDates AS D
  ON T.colonneDate = D.date
WHERE D.semaineDansLeMois = 2
AND D.annee = 2009

Une autre exemple : rechercher la consommation moyenne d’eau par semaine dans le mois, pour toute l’année 2009 :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
---------------------------------
-- Nicolas SOUQUET - 10/07/2009 -  
---------------------------------
SELECT semaineDansLeMois, AVG(consoSemaine)
FROM
(
  SELECT D.semaineDansLeMois, SUM(T.conso) AS consoSemaine
  FROM dbo.maTable AS T  
  JOIN dbo.TbDates AS D  
    ON T.colonneDate = D.date  
  WHERE D.annee = 2009
  GROUP BY D.semaineDansLeMois
) AS SUB (semaineDansLeMois, consoSemaine)
GROUP BY semaineDansLeMois

ElSuket

2 réflexions au sujet de « Créer une table de dates »

  1. Bonjour iberserk,

    Je pense que cela n’aucun impact; c’est plus une habitude de codage qui permet de trouver rapidement les colonnes dans une longue requête.
    En revanche il est possible que cela ait un impact si les colonnes sont aliasées (elles n’auraient pas le même nom dans la table dérivée que dans la requête qui la définit).

    Je teste et je vous tiens au courant :)

Laisser un commentaire