Bonjour,

Si j'ai une table suivant :

ID   ANNEE   PRIX
11   2016      11€
11   2017      12€
11   2020      20€
14   2016      09€
14   2017      10€
14   2020      20€

J'aimerais combler le tableau quand une année est manquante par le prix de l’année précédente :

ID   ANNEE   PRIX
11   2016      11€
11   2017      12€
11   2018      12€
11   2019      12€
11   2020      20€
14   2016      09€
14   2017      10€
14   2018      10€
14   2019      10€
14   2020      20€

J'ai pensé a utiliser un tableau d’année comme ceci :

WITH T_Tuple AS (
SELECT    1900 Col
UNION ALL
SELECT    Col+1 Col
FROM    T_Tuple
WHERE    Col+1 <= YEAR(GETDATE())
)

Ce qui me donne cela avec une jointure (QUI NE FONCTIONNE PAS) :

SELECT     
        T.id, T.annee, T_Tuple.Col,  T.prix
FROM    (SELECT id, annee, prix
			FROM MaTable
			ORDER BY id) T
			LEFT JOIN T_Tuple ON YEAR(T.annee) <= T_Tuple.Col 
ORDER BY T.id,T.annee, T_Tuple.Col

Mais cela ne donne pas le résultat voulu... Quelqu'un aurait une idée de comme procéder ?

Bonjour,

Ta récursive remonte la liste des années entre 1900 et 2020, ça fait beaucoup non ?

Ta jointure gauche ne sert à rien ici ... puisque tu rends "facultative" la récursive ...

Je ne comprends pas pour YEAR( T.annee) ... C'est déjà une année dans cette colonne non ?

Pour point de départ je simplifierai la récursive :

WITH liste_annee ( annee ) AS (
    SELECT 2000
	UNION ALL
    SELECT annee + 1
    FROM liste_annee
    WHERE annee <= YEAR( GETDATE() )
)
SELECT annee
FROM liste_annee;

Cela devrait te sortir la liste des nombres entiers (années) de 2000 à 2020.

Si on rajoute la liste des id de ta table avec un CROSS JOIN on va obtenir tous les couple id/année possible, avec le prix quand il existe :

WITH liste_annee ( annee ) AS (
    SELECT 2000
	UNION ALL
    SELECT annee + 1
    FROM liste_annee
    WHERE annee <= YEAR( GETDATE() )
)
SELECT
	I.id,
	A.annee,
	T.prix
FROM
	(
		-- Liste des id unique
		SELECT DISTINCT id
		FROM matable
		ORDER BY id
	) I
		-- Liste des années 
		CROSS JOIN liste_annee A
		LEFT JOIN matable T
			ON A.annee = T.annee
			AND I.id = T.id
ORDER BY T.id, A.annee

Maintenant on utilise la fonction LAG pour retrouver la valeur précédente :

WITH liste_annee ( annee ) AS (
    SELECT 2000
	UNION ALL
    SELECT annee + 1
    FROM liste_annee
    WHERE annee <= YEAR( GETDATE() )
)
SELECT
	I.id,
	A.annee,
	T.prix,
	LAG( T.prix, 1, 0 ) OVER ( ORDER BY I.id, A.annee ) AS precedent
FROM
	(
		-- Liste des id unique
		SELECT DISTINCT id
		FROM matable
		ORDER BY id
	) I
		-- Liste des années 
		CROSS JOIN liste_annee A
		LEFT JOIN matable T
			ON A.annee = T.annee
			AND I.id = T.id
ORDER BY T.id, A.annee

On doit presque toucher au but là non ?

-
Edité par Benzouye 14 septembre 2020 à 14:27:25

Merci effectivement on touche au but mais

La fonction LAG ne fonctionne pas a tous les coups..

Je ne comprends pas pourquoi des fois elle fonctionne et d'autre fois non

  WITH liste_annee ( annee ) AS (
    SELECT 2000
    UNION ALL
    SELECT annee + 1
    FROM liste_annee
    WHERE annee <= YEAR( GETDATE() )
)
SELECT
    I.id,
    A.annee,
    CASE 
		WHEN T.prix IS NULL THEN LAG( T.prix, 1, 0 ) OVER ( ORDER BY I.id, A.annee )
		ELSE T.prix
	END 'prix'
FROM
    (   -- Liste des id unique
        SELECT DISTINCT id
			  FROM MyTable 
			  --ORDER BY id
			) I
        -- Liste des années
        CROSS JOIN liste_annee A
        LEFT JOIN MyTable T
            ON A.annee = T.annee
            AND I.id = T.id
ORDER BY T.id, A.annee

Déjà petite correction sur le tri (ORDER BY), il vaudrait mieux utiliser I.id que T.id.

Et pour regarder ce qu'il se passe, je laisserai dans un premier temps les deux colonnes plutôt que de mettre le CASE :

WITH liste_annee ( annee ) AS (
    SELECT 2000
    UNION ALL
    SELECT annee + 1
    FROM liste_annee
    WHERE annee <= YEAR( GETDATE() )
)
SELECT
    I.id,
    A.annee,
    T.prix,
    LAG( T.prix, 1, 0 ) OVER ( ORDER BY I.id, A.annee ) AS precedent
FROM
    (
        -- Liste des id unique
        SELECT DISTINCT id
        FROM matable
    ) I
        -- Liste des années
        CROSS JOIN liste_annee A
        LEFT JOIN matable T
            ON A.annee = T.annee
            AND I.id = T.id
ORDER BY I.id, A.annee

La table T est en jointure gauche donc il se peut qu'elle ne contienne aucune valeur (NULL), cela peut perturber le tri.

Après je ne sais pas si cela peut corriger le problème ...