Discussion :

[Lemuel]

Bonjour,

Je suis en train de développer un reporting sur un système opérationnel et je me trouve actuellement devant un petit problème.

Dans l'application, j'ai une table de prix qui est censée être historisée... quand il y a changement de prix, il doit y avoir une Startdate et une Enddate pour l'ancien prix. Puis une nouvelle ligne avec le nouveau prix, la startdate du nouveau prix (qui doit être égale à la enddate de l'ancien) et une nouvelle enddate (1/1/9999 serait l'idéal).

Le problème que j'ai, c'est que les enddates ne sont pas remplies quand il y a un nouveau prix (les startdates bien). Ca n'a pas été géré automatiquement dans l'application (ça devrait être corrigé).

Ce que j'aimerais entre temps, c'est créer une vue sur base de cette table de prix avec les valeurs correctes pour les enddates.

Donc la règle est simple, si j'ai ça dans ma table :

prix - startdate - enddate
650 - 1/7/2006 - NULL
675 - 1/4/2007 - NULL
700 - 1/9/2007 - NULL

je dois avoir dans ma vue

prix - startdate - enddate
650 - 1/7/2006 - 1/4/2007
675 - 1/4/2007 - 1/9/2007
700 - 1/9/2007 - 1/1/9999

la enddate du record actuel = startdate du record suivant; si pas de record suivant : 1/1/9999

Comment est-ce que je peux traduire ça en SQL pour remplir ma vue sans devoir passer par un patch avec des curseurs?

Merci d'avance

[Waldar]

Bonjour,

Quel SGBD, quelle version ?

[Lemuel]

C'est en MySQL 5 que je développe ça.

[SQLpro]

Dans l'application, j'ai une table de prix qui est censée être historisée... quand il y a changement de prix, il doit y avoir une Startdate et une Enddate pour l'ancien prix. Puis une nouvelle ligne avec le nouveau prix, la startdate du nouveau prix (qui doit être égale à la enddate de l'ancien) et une nouvelle enddate (1/1/9999 serait l'idéal).

Votre modèle de données n'est pas bon. En effet il y a redondance dans le fait que la endate de la période précédente est la startdate (+- delta suivant l'inclusion de la borme) de la période suivante.
Vous ne devez donc pas avoir de colonne EndDate dans votre table, puisque cette colonne peut être calculée.
Au final votre table et votre vue doit ressembler à ceci :

La table :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
CREATE TABLE T_PRIX 
(Clef       INT NOT NULL, 
 StartDate  DATETIME NOT NULL, 
 Prix       DECIMAL(16,2),
 CONSTRAINT PK_PRX PRIMARY KEY (Clef, StartDate))

Des données de test :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
INSERT INTO T_PRIX VALUES (1, '1/7/2006', 650)
INSERT INTO T_PRIX VALUES (1, '1/4/2007', 675)
INSERT INTO T_PRIX VALUES (1, '1/9/2007', 700)
INSERT INTO T_PRIX VALUES (2, '1/6/2006', 1650)
INSERT INTO T_PRIX VALUES (2, '1/2/2008', 1975)

La vue :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
CREATE VIEW V_PRIX
AS
SELECT Clef, StartDate, 
       COALESCE((SELECT MIN(P2.StartDate) - 1 DAY 
                 FROM   T_PRIX AS P2
                 WHERE  P2.Clef = P1.Clef
                   AND  P2.StartDate > P1.StartDate), 
               '31/12/9999') AS EndDate, 
       Prix
FROM   T_PRIX AS P1

Test :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
SELECT *
FROM   V_PRIX

Résultat :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
Clef        StartDate       EndDate         Prix
----------- --------------- --------------- -------------
1           2006-07-01      2007-03-31      650.00
1           2007-04-01      2007-08-31      675.00
1           2007-09-01      9999-12-31      700.00
2           2006-06-01      2008-01-31      1650.00
2           2008-02-01      9999-12-31      1975.00

Bref, apprenez SQL, mon site web, comme mes bouquins, peuvent vous y aider !

A +

[Lemuel]

Merci pour votre intervention.
Effectivement, le modèle de données sur lequel nous travaillons a été développé par une autre équipe que la nôtre (celle qui a mis l'application web sur pied) et vu que la enddate 'peut' être insérée à l'avance (on peut définir que dans 3 mois, le prix ne sera plus d'application), le champ enddate dans cette table était obligatoire.
La vue que nous créons permet de remplacer les 'null' (pas de contrainte sur les enddates, ça devrait être géré à la prochaine version de l'application) et de pouvoir enfin faire les calculs qui nous intéressent pour le reporting.

[Waldar]

La vue que nous créons permet de remplacer les 'null' (pas de contrainte sur les enddates, ça devrait être géré à la prochaine version de l'application) et de pouvoir enfin faire les calculs qui nous intéressent pour le reporting.

Vous faites votre reporting directement sur la base applicative de production ?