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
|
select
AM_LP.annee AS "Année",
AM_LP.article AS "Article",
sum(case when JAN.quantite is null then 0 else JAN.quantite end) as "Janvier",
sum(case when FEV.quantite is null then 0 else FEV.quantite end) as "Février",
sum(case when MAR.quantite is null then 0 else MAR.quantite end) as "Mars",
sum(case when AVR.quantite is null then 0 else AVR.quantite end) as "Avril",
sum(case when MAI.quantite is null then 0 else MAI.quantite end) as "Mai",
sum(case when JUN.quantite is null then 0 else JUN.quantite end) as "Juin",
sum(case when JUI.quantite is null then 0 else JUI.quantite end) as "Juillet",
sum(case when AOU.quantite is null then 0 else AOU.quantite end) as "Aout",
sum(case when SEP.quantite is null then 0 else SEP.quantite end) as "Septembre",
sum(case when OKT.quantite is null then 0 else OKT.quantite end) as "Octobre",
sum(case when NOV.quantite is null then 0 else NOV.quantite end) as "Novembre",
sum(case when DEZ.quantite is null then 0 else DEZ.quantite end) as "Décembre"
from
(
select
annee, article
from xannee
inner join xarticle XA on 1=1
where annee = 2018
) AM_LP
left join (select * from xproduit where month(daterec) = 1) JAN on year(JAN.daterec) = AM_LP.annee and JAN.article = AM_LP.article
left join (select * from xproduit where month(daterec) = 2) FEV on year(FEV.daterec) = AM_LP.annee and FEV.article = AM_LP.article
left join (select * from xproduit where month(daterec) = 3) MAR on year(MAR.daterec) = AM_LP.annee and MAR.article = AM_LP.article
left join (select * from xproduit where month(daterec) = 4) AVR on year(AVR.daterec) = AM_LP.annee and AVR.article = AM_LP.article
left join (select * from xproduit where month(daterec) = 5) MAI on year(MAI.daterec) = AM_LP.annee and MAI.article = AM_LP.article
left join (select * from xproduit where month(daterec) = 6) JUN on year(JUN.daterec) = AM_LP.annee and JUN.article = AM_LP.article
left join (select * from xproduit where month(daterec) = 7) JUI on year(JUI.daterec) = AM_LP.annee and JUI.article = AM_LP.article
left join (select * from xproduit where month(daterec) = 8) AOU on year(AOU.daterec) = AM_LP.annee and AOU.article = AM_LP.article
left join (select * from xproduit where month(daterec) = 9) SEP on year(SEP.daterec) = AM_LP.annee and SEP.article = AM_LP.article
left join (select * from xproduit where month(daterec) = 10) OKT on year(OKT.daterec) = AM_LP.annee and OKT.article = AM_LP.article
left join (select * from xproduit where month(daterec) = 11) NOV on year(NOV.daterec) = AM_LP.annee and NOV.article = AM_LP.article
left join (select * from xproduit where month(daterec) = 12) DEZ on year(DEZ.daterec) = AM_LP.annee and DEZ.article = AM_LP.article
group by AM_LP.annee, AM_LP.article |
Partager