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
| SELECT 1, RATIO.RAT_Valeur, RATIO.RAT_Periode
FROM RATIO INNER JOIN CENTRE ON RATIO.CEN_Code_FK = CENTRE.CEN_Code
WHERE (RATIO.RAT_Periode BETWEEN '2010/12' AND '2011/11') AND (CENTRE.CEN_Ville = 'Ville') AND (RATIO.RAT_Nom = 'Nom')
UNION
SELECT 2, CAST(AVG(RATIO.RAT_Valeur)AS DECIMAL(15,2)) AS Moyenne , RATIO.RAT_Periode
FROM RATIO
WHERE (RATIO.RAT_Periode BETWEEN '2010/12' AND '2011/11') AND (RATIO.RAT_Nom = 'Nom')
GROUP BY RATIO.RAT_Periode
UNION
SELECT 3, CAST (AVG(RAT_Valeur) AS DECIMAL(15,2)) AS MOY_Basse, RAT_Periode
FROM ( SELECT RAT_Valeur, RANK() OVER(PARTITION BY RAT_Periode ORDER BY RATIO.RAT_Valeur ASC) AS Rank_Bas, RAT_Periode
FROM RATIO
WHERE RAT_Periode BETWEEN '2010/12' AND '2011/11' AND RAT_Nom='Nom') AS TAB_MOY_BASSE
WHERE Rank_Bas <=3
GROUP BY RAT_Periode
UNION
SELECT 4, CAST (AVG(RAT_Valeur) AS DECIMAL(15,2)) AS MOY_Haute, RAT_Periode
FROM ( SELECT RAT_Valeur, RANK() OVER(PARTITION BY RAT_Periode ORDER BY RATIO.RAT_Valeur DESC) AS Rank_Haut, RAT_Periode
FROM RATIO
WHERE RAT_Periode BETWEEN '2010/12' AND '2011/11' AND RAT_Nom='Nom' ) AS TAB_MOY_HAUT
WHERE Rank_Haut <=3
GROUP BY RAT_Periode |
Partager