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
| SELECT
A.id_code,
A.code AS CODE,
A.intitule_code AS NOM,
B.libelle AS GROUPE,
CASE WHEN id_parent = 0 THEN num_cpte
Else codcpt || num_cpte
END AS sous_compte,
SUM(CASE WHEN E.id = 1 THEN 1 Else 0 END) AS "PC1",
SUM(CASE WHEN E.id = 2 THEN 1 Else 0 END) AS "PC2",
SUM(CASE WHEN E.id = 3 THEN 1 Else 0 END) AS "PC3",
SUM(CASE WHEN E.id = 4 THEN 1 Else 0 END) AS "PC4",
SUM(CASE WHEN E.id = 5 THEN 1 Else 0 END) AS "PC5",
SUM(CASE WHEN codcpt || num_cpte = F2013.cptdeb AND A.id_code = F2013.code_1 THEN F2013.montant Else 0 END) AS A2013
-- SUM(CASE WHEN codcpt || num_cpte = F.cptdeb AND A.id_code = F.code_1 THEN F.montant Else 0 END) AS A2014
FROM
base.code_1 AS A
LEFT OUTER JOIN base.asso_code_1_sc AS C
ON A.id_code = C.id_code AND A.id_groupe = C.id_groupe
LEFT JOIN base.plancpt_lib AS E
ON C.id_plan = E.id
LEFT JOIN base.sous_compte AS D
ON D.id_souscompte = CAST (C.no_compte AS integer)
LEFT OUTER JOIN base.r2013mou AS F2013
ON A.id_code = F2013.code_1 AND codcpt || num_cpte = F2013.cptdeb
LEFT OUTER JOIN base.rmouvt AS F
ON A.id_code = F.code_1 AND codcpt || num_cpte = F.cptdeb
-- LEFT JOIN base.groupes_codes_adm AS B
-- ON A.id_groupe = B.id AND B.id = C.id_groupe
WHERE A.id_code = 54 OR A.id_code = 28 OR A.id_code = 410
GROUP BY A.id_code,CODE,NOM,GROUPE,sous_compte
ORDER BY 1; |
Partager