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 30 31
| CREATE TABLE MaTable
(ANNEE INT,
MOIS INT,
ID INT,
VALEUR INT)
INSERT INTO MaTable VALUES (2006, 01, 1, 15)
INSERT INTO MaTable VALUES (2006, 02, 1, 16)
INSERT INTO MaTable VALUES (2006, 03, 1, 25)
INSERT INTO MaTable VALUES (2006, 12, 1, 30)
INSERT INTO MaTable VALUES (2006, 01, 2, 5)
INSERT INTO MaTable VALUES (2006, 02, 2, 15)
INSERT INTO MaTable VALUES (2006, 12, 2, 35)
SELECT ANNEE, MOIS, ID, VALEUR, VALEUR - COALESCE(PRED, 0) AS VALEUR_DELTA
FROM
(
SELECT *,
(SELECT TT.VALEUR
FROM MaTable TT
WHERE T.ID = TT.ID
AND (ANNEE * 100 + MOIS) = (SELECT MAX(TTT.ANNEE * 100 + TTT.MOIS)
FROM MaTable TTT
WHERE T.ID = TT.ID
AND (TTT.ANNEE * 100 + TTT.MOIS) < (T.ANNEE * 100 + T.MOIS))
) AS PRED
FROM MaTable T
) TTTT
ORDER BY ID, ANNEE, MOIS |
Partager