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 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91
| WITH ass_ca_sc AS
(
SELECT CA.code AS code_A
,Gr.id AS GC
,CASE WHEN id_parent = 0 THEN num_cpte
Else codcpt || num_cpte
END AS sous_compte
,A_CA_SC.id_plan AS AS_PC
FROM base.asso_code_A_sc AS A_CA_SC
RIGHT JOIN base.code_A AS CA
ON A_CA_SC.id_code = CA.id_code
LEFT JOIN base.groupes_codes AS Gr
ON CA.id_groupe = Gr.id
LEFT JOIN base.sous_compte AS SC
ON A_CA_SC.id_plan = SC.id_plan AND CAST (A_CA_SC.no_compte AS INTEGER) = SC.id_souscompte
GROUP BY CA.id_code, code_A, GC, sous_compte, AS_PC
ORDER BY code_A, sous_compte asc
),
Somme_2014 AS
(
SELECT CA.code AS code_A
,CASE WHEN id_parent = 0 THEN num_cpte
Else codcpt || num_cpte
END AS sous_compte
,SUM( CASE WHEN Gr.type_depenses_rec = 1 AND Rm.montant < 0 THEN
CASE WHEN (CASE WHEN SC.id_parent = 0 THEN SC.num_cpte
Else SC.codcpt || SC.num_cpte
END) = Rm.cptdeb THEN Rm.montant
Else 0 END
WHEN Gr.type_depenses_rec = 2 AND Rm.montant < 0 THEN
CASE WHEN (CASE WHEN SC.id_parent = 0 THEN SC.num_cpte
Else SC.codcpt || SC.num_cpte
END) = Rm.cptcred THEN Rm.montant
Else 0 END
ELSE 0
END) AS mA2014
,SUM( CASE WHEN Gr.type_depenses_rec = 1 AND Rm.montant > 0 THEN
CASE WHEN (CASE WHEN SC.id_parent = 0 THEN SC.num_cpte
Else SC.codcpt || SC.num_cpte
END) = Rm.cptdeb THEN Rm.montant
Else 0 END
WHEN Gr.type_depenses_rec = 2 AND Rm.montant > 0 THEN
CASE WHEN (CASE WHEN SC.id_parent = 0 THEN SC.num_cpte
Else SC.codcpt || SC.num_cpte
END) = Rm.cptcred THEN Rm.montant
Else 0 END
ELSE 0
END) AS pA2014
FROM base.asso_code_A_sc AS A_CA_SC
RIGHT JOIN base.code_A AS CA
ON A_CA_SC.id_code = CA.id_code
LEFT JOIN base.groupes_codes AS Gr
ON CA.id_groupe = Gr.id
LEFT JOIN base.sous_compte AS SC
ON CAST(A_CA_SC.no_compte AS INTEGER) = SC.id_souscompte AND A_CA_SC.id_plan = SC.id_plan
LEFT JOIN base.rmo AS Rm
ON A_CA_SC.id_code = Rm.code_a AND CASE WHEN Gr.type_depenses_rec = 1 THEN
CASE WHEN SC.id_parent = 0 THEN SC.num_cpte
Else SC.codcpt || SC.num_cpte
END = Rm.cptdeb
ELSE CASE WHEN SC.id_parent = 0 THEN SC.num_cpte
Else SC.codcpt || SC.num_cpte
END = Rm.cptcred
END
GROUP BY code_A, sous_compte
ORDER BY code_A, sous_compte asc
)
SELECT
ass_ca_sc.code_A
,ass_ca_sc.sous_compte
,SUM(CASE WHEN GC = 1 THEN 1 ELSE 0 END) AS CA_depensesS
,SUM(CASE WHEN GC = 2 THEN 1 ELSE 0 END) AS CB_depensesS
,SUM(CASE WHEN GC = 3 THEN 1 ELSE 0 END) AS CA_RECETTES
,SUM(CASE WHEN GC = 4 THEN 1 ELSE 0 END) AS CA_RECETTES
,SUM(CASE WHEN AS_PC = 1 THEN 1 ELSE 0 END) AS "PC1"
,SUM(CASE WHEN AS_PC = 2 THEN 1 ELSE 0 END) AS "PC2"
,SUM(CASE WHEN AS_PC = 3 THEN 1 ELSE 0 END) AS "PC3"
,SUM(CASE WHEN AS_PC = 4 THEN 1 ELSE 0 END) AS "PC4"
,SUM(CASE WHEN AS_PC = 5 THEN 1 ELSE 0 END) AS "PC5"
,SUM(CASE WHEN S2014.mA2014 <> 0 THEN S2014.mA2014 ELSE 0 END) AS "-2014"
,SUM(CASE WHEN S2014.pA2014 <> 0 THEN S2014.pA2014 ELSE 0 END) AS "+2014"
FROM ass_ca_sc
LEFT JOIN Somme_2014 AS S2014
ON ass_ca_sc.code_A = S2014.code_A AND ass_ca_sc.sous_compte = S2014.sous_compte
GROUP BY ass_ca_sc.code_A
,ass_ca_sc.sous_compte
ORDER BY CA_depensesS
,ass_ca_sc.code_A
,ass_ca_sc.sous_compte
asc; |
Partager