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 37 38 39 40 41 42 43 44 45 46 47 48 49
|
SELECT
A0.ID_AUTO,
A0.LBLPRODUIT
, t.CA
, sum(t2.ca) AS pcumul1
, 80 as Limite
, (((t2.ca / t3.ca_tot) * 100)) AS pcumul
FROM MODELE.ARTICLE A0
join (
SELECT S0.ID_PRODUIT,
round(sum(S0.MTTOTALTTC),0) AS CA
FROM VENTE.PRODUIT_LIV_CLIENT S0
INNER JOIN [VENTE].[LIVRAISON_CLIENT] RF
ON [S0].[ID_AUTO] = [RF].[ID_AUTO]
WHERE convert(date,RF.DATEPIECE) BETWEEN '2010-01-01' AND '2017-01-01'
GROUP BY S0.ID_PRODUIT
) t on A0.ID_AUTO = t.ID_PRODUIT
JOIN (
SELECT S0.ID_PRODUIT,
round(sum(S0.MTTOTALTTC),0) AS CA
FROM VENTE.PRODUIT_LIV_CLIENT S0
INNER JOIN [VENTE].[LIVRAISON_CLIENT] RF
ON [S0].[ID_LIVR_CLIENT] = [RF].[ID_AUTO]
WHERE convert(date,RF.DATEPIECE) BETWEEN '2010-01-01' AND '2017-01-01'
GROUP BY S0.ID_PRODUIT
) t2 ON t2.ca >= t.ca
CROSS JOIN (
SELECT round(sum(S0.MTTOTALTTC),0) AS ca_tot
FROM VENTE.PRODUIT_LIV_CLIENT S0
INNER JOIN [VENTE].[LIVRAISON_CLIENT] RF
ON [S0].[ID_LIVR_CLIENT] = [RF].[ID_AUTO]
WHERE convert(date,RF.DATEPIECE) BETWEEN '2010-01-01' AND '2018-01-01'
) t3
GROUP BY
A0.ID_AUTO
, A0.LBLPRODUIT
, t.CA
, t2.CA
, t3.ca_tot
ORDER BY CA DESC |
Partager