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 32 33 34 35 36
| WITH CTE AS (
select DISTINCT
GType
, Datepart(MM,datreal) as Mois
, sum(Qreb) OVER(PARTITION BY GType, mois) AS sREB
from dbo.CID06_Analyse_des_Rebuts
where datreal >= DATEADD(YEAR , DATEDIFF(YEAR, 0, GETDATE()), 0) -- Annee en cours
and UP = '5'
and Qbon + Qreb > 0
group by Gtype,datreal,mois,Qreb --order by gtype,mois
),
Prec AS (
SELECT
GType
, Mois
, sREB
, CASE WHEN Mois%2 = 1
THEN
MAX(CASE WHEN Mois%2 = 0 THEN sREB END) OVER(PARTITION BY Mois/2)
ELSE
MAX(CASE WHEN Mois%2 = 1 THEN sREB END) OVER(PARTITION BY (Mois-1)/2)
END AS Precedent
FROM CTE
)
SELECT
GType
, Mois
, sREB
, Precedent
, ISNULL((sREB - Precedent) / (Precedent / 100.),'0') AS Evolution
FROM Prec
ORDER BY Gtype, mois |
Partager