1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
| SELECT NE.nature_engagement, EE.n_nature_engagement, EE.montant_engager,
SUM(CASE WHEN DATALENGTH(COALESCE(ME.n_certification2, '')) = 0 THEN 0 ELSE ISNULL(ME.montant, 0) END) AS modification_engager,
EE.montant_engager + SUM(CASE WHEN DATALENGTH(COALESCE (ME.n_certification2, '')) = 0 THEN 0 ELSE ISNULL(ME.montant, 0) END) AS total_engager,
ISNULL( ( SELECT SUM(ISNULL(montant_mandater, 0)) AS total_mandater
FROM dbo.etat_mandatement AS EM
WHERE (EE.id2 = id2) AND (date_emis_mandatement NOT LIKE '')
GROUP BY id2 ),0 ) AS total_mandater
FROM dbo.etat_budget AS EB
INNER JOIN dbo.etat_engagement AS EE
ON EB.id1 = EE.id1
INNER JOIN dbo.nature_engagement AS NE
ON EE.id_nature_engagement = NE.id_nature_engagement
INNER JOIN dbo.rubrique_budgetaire AS RB
ON EB.id_rubrique = RB.id_rubrique
LEFT OUTER JOIN dbo.modification_engager AS ME
ON EE.id2 = ME.id2
WHERE (EE.n_certification NOT LIKE '')
GROUP BY NE.nature_engagement, EE.n_nature_engagement, EE.montant_engager, EE.id2 |
Partager