J'ai cette requête qui me permet de sortir un rapport de chiffre d'affaire comparatif par mois et par boutique .
Aujourd'hui j'aimerais avoir ce rapport en cumulant 02 mois ( jan-fev)
sauf que j'aimerais l'avoir sur deux mois.
est ce que quelqu'un peut m'aider
ci-dessous ma requete
SELECT d.day AS DATE_N1,
d.month AS MONTH_N1,
((?ANNEE;N;2023;L1C1?)-1) AS ANNE_N1,
(CASE WHEN c3.montant IS NULL THEN 0 ELSE c3.montant END) AS CA_N1,
d.day AS DATE_N2,
d.month AS MONTH_N2,
(?ANNEE;N;2023;L1C1?) AS ANNE_N2,
(CASE WHEN c5.montant IS NULL THEN 0 ELSE c5.montant END) AS CA_N,
?BOUTIQUE;CETABLISSEMENT;;L3C1? AS ETAB
FROM
(
SELECT DISTINCT DAY(c1.GL_DATEPIECE) AS day, MONTH(c1.GL_DATEPIECE) AS month
FROM LIGNE c1
WHERE c1.GL_NATUREPIECEG="FFO"
AND MONTH(c1.GL_DATEPIECE)=?MOIS;N;0;L2C1?
AND c1.GL_FAMILLENIV1 NOT IN ("","008","009")
AND c1.GL_FAMILLENIV2 NOT IN ("","136","180")
AND c1.GL_FAMILLENIV3 NOT IN ("003","155")
AND c1.GL_TYPEARTICLE="MAR"
AND c1.GL_ETABLISSEMENT=?BOUTIQUE;CETABLISSEMENT;;L3C1?
ORDER BY MONTH(c1.GL_DATEPIECE),DAY(c1.GL_DATEPIECE)
) d
LEFT JOIN
(
SELECT c2.GL_DATEPIECE, sum(c2.GL_TOTALHT) AS montant
FROM LIGNE c2
WHERE YEAR(c2.GL_DATEPIECE) = (?ANNEE;N;2023;L1C1?)-1
AND c2.GL_NATUREPIECEG="FFO"
AND MONTH(c2.GL_DATEPIECE)=?MOIS;N;0;L2C1?
AND c2.GL_FAMILLENIV1 NOT IN ("","008","009")
AND c2.GL_FAMILLENIV2 NOT IN ("","136","180")
AND c2.GL_FAMILLENIV3 NOT IN ("003","155")
AND c2.GL_TYPEARTICLE="MAR"
AND c2.GL_ETABLISSEMENT=?BOUTIQUE;CETABLISSEMENT;;L3C1?
GROUP BY c2.GL_DATEPIECE
) c3 ON d.day = DAY(c3.GL_DATEPIECE) AND d.month = MONTH(c3.GL_DATEPIECE)
LEFT JOIN
(
SELECT c4.GL_DATEPIECE, sum(c4.GL_TOTALHT) AS montant
FROM LIGNE c4
WHERE YEAR(c4.GL_DATEPIECE) = ?ANNEE;N;2023;L1C1?
AND c4.GL_NATUREPIECEG="FFO"
AND MONTH(c4.GL_DATEPIECE)=?MOIS;N;0;L2C1?
AND c4.GL_FAMILLENIV1 NOT IN ("","008","009")
AND c4.GL_FAMILLENIV2 NOT IN ("","136","180")
AND c4.GL_FAMILLENIV3 NOT IN ("003","155")
AND c4.GL_TYPEARTICLE="MAR"
AND c4.GL_ETABLISSEMENT=?BOUTIQUE;CETABLISSEMENT;;L3C1?
GROUP BY c4.GL_DATEPIECE
) c5 ON d.day = DAY(c5.GL_DATEPIECE) AND d.month = MONTH(c5.GL_DATEPIECE)
ORDER BY d.day ASC
Partager