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
|
Declare @annee as int
set @annee = 2010
SELECT
C.NOM_CLIENT,
OCT = SUM(CASE WHEN MONTH(SCD.DATE)=10 AND YEAR(SCD.DATE)=@ANNEE AND SCD.DATE >= getdate() THEN SCD.MONTANT_EURO ELSE 0 END),
NOV = SUM(CASE WHEN MONTH(SCD.DATE)=11 AND YEAR(SCD.DATE)=@ANNEE AND SCD.DATE >= getdate() THEN SCD.MONTANT_EURO ELSE 0 END),
DEC = SUM(CASE WHEN MONTH(SCD.DATE)=12 AND YEAR(SCD.DATE)=@ANNEE AND SCD.DATE >= getdate() THEN SCD.MONTANT_EURO ELSE 0 END),
JANV = SUM(CASE WHEN MONTH(SCD.DATE)=1 AND YEAR(SCD.DATE)=@ANNEE+1 AND SCD.DATE >= getdate() THEN SCD.MONTANT_EURO ELSE 0 END),
FEV = SUM(CASE WHEN MONTH(SCD.DATE)=2 AND YEAR(SCD.DATE)=@ANNEE+1 AND SCD.DATE >= getdate() THEN SCD.MONTANT_EURO ELSE 0 END),
MARS = SUM(CASE WHEN MONTH(SCD.DATE)=3 AND YEAR(SCD.DATE)=@ANNEE+1 AND SCD.DATE >= getdate() THEN SCD.MONTANT_EURO ELSE 0 END),
AVRIL = SUM(CASE WHEN MONTH(SCD.DATE)=4 AND YEAR(SCD.DATE)=@ANNEE+1 AND SCD.DATE >= getdate() THEN SCD.MONTANT_EURO ELSE 0 END),
MAI = SUM(CASE WHEN MONTH(SCD.DATE)=5 AND YEAR(SCD.DATE)=@ANNEE+1 AND SCD.DATE >= getdate() THEN SCD.MONTANT_EURO ELSE 0 END),
JUIN = SUM(CASE WHEN MONTH(SCD.DATE)=6 AND YEAR(SCD.DATE)=@ANNEE+1 AND SCD.DATE >= getdate() THEN SCD.MONTANT_EURO ELSE 0 END),
JUIL = SUM(CASE WHEN MONTH(SCD.DATE)=7 AND YEAR(SCD.DATE)=@ANNEE+1 AND SCD.DATE >= getdate() THEN SCD.MONTANT_EURO ELSE 0 END),
AOUT = SUM(CASE WHEN MONTH(SCD.DATE)=8 AND YEAR(SCD.DATE)=@ANNEE+1 AND SCD.DATE >= getdate() THEN SCD.MONTANT_EURO ELSE 0 END),
SEPT = SUM(CASE WHEN MONTH(SCD.DATE)=9 AND YEAR(SCD.DATE)=@ANNEE+1 AND SCD.DATE >= getdate() THEN SCD.MONTANT_EURO ELSE 0 END),
TOTAL = SUM(CASE WHEN (MONTH(SCD.DATE)>=10 AND (YEAR(SCD.DATE)=@ANNEE) OR (MONTH(SCD.DATE) <= 9 AND YEAR(SCD.DATE)=@ANNEE + 1)) AND SCD.DATE >= getdate() THEN SCD.MONTANT_EURO ELSE 0 END)
FROM SCD, CDE, CLIENT C, AFFAIRE A
WHERE SCD.N_CDE_CLI = CDE.N_CDE_CLI
AND CDE.N_affaire = A.N_affaire
AND A.N_CLIENT = C.N_CLIENT
GROUP BY C.NOM_CLIENT
UNION ALL
SELECT
C.NOM_CLIENT,
OCT = SUM(CASE WHEN MONTH(DATE_FACTURE)=10 AND YEAR(DATE_FACTURE)=@ANNEE AND DATE_FACTURE < getdate() THEN (CASE WHEN F.AVOIR='OUI' THEN -HT_EURO ELSE HT_EURO END) ELSE 0 END),
NOV = SUM(CASE WHEN MONTH(DATE_FACTURE)=11 AND YEAR(DATE_FACTURE)=@ANNEE AND DATE_FACTURE < getdate() THEN (CASE WHEN F.AVOIR='OUI' THEN -HT_EURO ELSE HT_EURO END) ELSE 0 END),
DEC = SUM(CASE WHEN MONTH(DATE_FACTURE)=12 AND YEAR(DATE_FACTURE)=@ANNEE AND DATE_FACTURE < getdate() THEN (CASE WHEN F.AVOIR='OUI' THEN -HT_EURO ELSE HT_EURO END) ELSE 0 END),
JANV = SUM(CASE WHEN MONTH(DATE_FACTURE)=1 AND YEAR(DATE_FACTURE)=@ANNEE+1 AND DATE_FACTURE < getdate() THEN (CASE WHEN F.AVOIR='OUI' THEN -HT_EURO ELSE HT_EURO END) ELSE 0 END),
FEV = SUM(CASE WHEN MONTH(DATE_FACTURE)=2 AND YEAR(DATE_FACTURE)=@ANNEE+1 AND DATE_FACTURE < getdate() THEN (CASE WHEN F.AVOIR='OUI' THEN -HT_EURO ELSE HT_EURO END) ELSE 0 END),
MARS = SUM(CASE WHEN MONTH(DATE_FACTURE)=3 AND YEAR(DATE_FACTURE)=@ANNEE+1 AND DATE_FACTURE < getdate() THEN (CASE WHEN F.AVOIR='OUI' THEN -HT_EURO ELSE HT_EURO END) ELSE 0 END),
AVRIL = SUM(CASE WHEN MONTH(DATE_FACTURE)=4 AND YEAR(DATE_FACTURE)=@ANNEE+1 AND DATE_FACTURE < getdate() THEN (CASE WHEN F.AVOIR='OUI' THEN -HT_EURO ELSE HT_EURO END) ELSE 0 END),
MAI = SUM(CASE WHEN MONTH(DATE_FACTURE)=5 AND YEAR(DATE_FACTURE)=@ANNEE+1 AND DATE_FACTURE < getdate() THEN (CASE WHEN F.AVOIR='OUI' THEN -HT_EURO ELSE HT_EURO END) ELSE 0 END),
JUIN = SUM(CASE WHEN MONTH(DATE_FACTURE)=6 AND YEAR(DATE_FACTURE)=@ANNEE+1 AND DATE_FACTURE < getdate() THEN (CASE WHEN F.AVOIR='OUI' THEN -HT_EURO ELSE HT_EURO END) ELSE 0 END),
JUIL = SUM(CASE WHEN MONTH(DATE_FACTURE)=7 AND YEAR(DATE_FACTURE)=@ANNEE+1 AND DATE_FACTURE < getdate() THEN (CASE WHEN F.AVOIR='OUI' THEN -HT_EURO ELSE HT_EURO END) ELSE 0 END),
AOUT = SUM(CASE WHEN MONTH(DATE_FACTURE)=8 AND YEAR(DATE_FACTURE)=@ANNEE+1 AND DATE_FACTURE < getdate() THEN (CASE WHEN F.AVOIR='OUI' THEN -HT_EURO ELSE HT_EURO END) ELSE 0 END),
SEPT = SUM(CASE WHEN MONTH(DATE_FACTURE)=9 AND YEAR(DATE_FACTURE)=@ANNEE+1 AND DATE_FACTURE < getdate() THEN (CASE WHEN F.AVOIR='OUI' THEN -HT_EURO ELSE HT_EURO END) ELSE 0 END),
TOTAL = SUM(CASE WHEN (MONTH(DATE_FACTURE)>=10 AND (YEAR(DATE_FACTURE)=@ANNEE) OR (MONTH(DATE_FACTURE) <= 9 AND YEAR(DATE_FACTURE)=@ANNEE + 1)) AND DATE_FACTURE < getdate() THEN (CASE WHEN F.AVOIR='OUI' THEN -HT_EURO ELSE HT_EURO END) ELSE 0 END)
FROM FACTURE F, CLIENT C
WHERE F.N_CLIENT = C.N_CLIENT
GROUP BY C.NOM_CLIENT
ORDER BY C.NOM_CLIENT |
Partager