ReqNORDTOT

SELECT SUM(D_CANETTTC) AS D_CANETTTC, SUM(D_CANETTTCN1) AS D_CANETTTCN1,
SUM(D_CAARRHESN) AS D_CAARRHESN, SUM(D_CAARRHESN1) AS D_CAARRHESN1,
SUM(D_VOLVENTES) AS D_VOLVENTES, SUM(D_VOLVENTESN1) AS D_VOLVENTESN1,
SUM(D_NBTICKETS) AS D_NBTICKETS, SUM(D_NBTICKETSN1) AS D_NBTICKETSN1,
SUM(D_NBVISITEURS) AS D_NBVISITEURS, SUM(D_NBVISITEURSN1) AS D_NBVISITEURSN1,
DECODE(SUM(D_NBVISITEURS),0,0,SUM(DECODE(D_NBVISITEURS,0,0,D_NBTICKETS))/SUM(D_NBVISITEURS)) AS TT,
DECODE(SUM(D_NBVISITEURSN1),0,0,SUM(DECODE(D_NBVISITEURSN1,0,0,D_NBTICKETSN1))/SUM(D_NBVISITEURSN1)) AS TTN1,
DECODE(SUM(D_NBTICKETS),0,0, SUM(DECODE(D_NBTICKETS,0,0,D_VOLVENTES))/SUM(D_NBTICKETS)) AS IV,
DECODE(SUM(D_NBTICKETSN1),0,0, SUM(DECODE(D_NBTICKETSN1,0,0,D_VOLVENTESN1))/SUM(D_NBTICKETSN1)) AS IVN1,
DECODE(SUM(D_NBTICKETS),0,0, SUM(DECODE(D_NBTICKETS,0,0,D_CANETTTC))/SUM(D_NBTICKETS)) AS PM,
DECODE(SUM(D_NBTICKETSN1),0,0, SUM(DECODE(D_NBTICKETSN1,0,0,D_CANETTTCN1))/SUM(D_NBTICKETSN1)) AS PMN1,
SUM(D_OBJECTIFS) AS D_OBJECTIFS, SUM(D_NBHEURES) AS D_NBHEURES,  SUM(D_NBMAILOK) AS D_NBMAILOK
FROM
(SELECT   mk.codemagasin,
             SUM(DECODE(vd.nature,'D',MK.CANETTTC,0)) D_CANETTTC,    SUM(DECODE(vd.nature,'D',MK.CANETTTCN1,0)) D_CANETTTCN1,
SUM(DECODE(vd.nature,'D',MK.CAARRHESN,0)) D_CAARRHESN,    SUM(DECODE(vd.nature,'D',MK.CAARRHESN1,0)) D_CAARRHESN1,
             SUM(DECODE(vd.nature,'D',MK.VOLVENTES,0)) D_VOLVENTES,  SUM(DECODE(vd.nature,'D',MK.VOLVENTESN1,0)) D_VOLVENTESN1,
             SUM(DECODE(vd.nature,'D',MK.NBTICKETS,0)) D_NBTICKETS, SUM(DECODE(vd.nature,'D',MK.NBTICKETSN1,0)) D_NBTICKETSN1,
             SUM(DECODE(vd.nature,'D',MK.NBVISITEURS,0)) D_NBVISITEURS, SUM(DECODE(vd.nature,'D',MK.NBVISITEURSN1,0)) D_NBVISITEURSN1,
             SUM(DECODE(vd.nature,'D',MK.OBJECTIFS,0)) D_OBJECTIFS,
             SUM(DECODE(vd.nature,'D',MK.NBHEURESOUVERTURE,0)) D_NBHEURES,
             SUM(DECODE(vd.nature,'D',MK.NBMAILOK,0)) D_NBMAILOK,
             SUM(MK.CANETTTCN1+MK.CAARRHESN1) Y_CANETTTCN1
    FROM MADURA_KPI MK, MAGASINS M, STATS_MAGASIN SM,
          (SELECT DECODE(S.LADATE, TO_DATE( :DebutDAY ), 'D', DECODE(SIGN( (S.LADATE+1) - TO_DATE( :DebutMTD )), 1, 'M', 'Y')) NATURE, S.LADATE
            FROM SR_CALENDRIER S
            WHERE S.LADATE >= TO_DATE(:DebutYTD) AND s.LADATE=TO_DATE(:DebutDAY)
            AND S.LADATE <= TO_DATE(:FinYTD)) VD
WHERE M.CODEMAGASIN = MK.CODEMAGASIN AND SM.CODEMAGASIN = M.CODEMAGASIN AND SM.CODEAXESTAT=18
AND SM.CODEELEMENTSTAT = 2
AND MK.TYPEDEMAGASIN = 1 -- Magasin en Propre.
AND MK.JOURDEVENTE = VD.LaDate --and VD.NATURE='D'
--AND ((M.DATEFERMETURE IS NULL) OR (M.DATEFERMETURE > :DebutYTDN1 ))
AND M.DATEFERMETURE IS NULL
--AND M.CODEMAGASIN NOT IN ( :MagExclus )
AND mk.magconstant=1
GROUP BY mk.codemagasin)