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
| SELECT '0' bigTot, '1' orderline, substr(c.num, 0, 2) as classe, substr(c.num, 0, 4) as sousclasse, c.num as compte, c.libelle as libCompte, COALESCE(SUM(b.debit), 0) as debitTot, COALESCE(SUM(b.credit), 0) as creditTot,
CASE WHEN COALESCE(SUM(b.debit), 0) >= COALESCE(SUM(b.credit), 0) THEN printf('%.2f', COALESCE(SUM(b.debit), 0) - COALESCE(SUM(b.credit), 0)) ELSE '0' END as soldeDebit,
CASE WHEN COALESCE(SUM(b.credit), 0) >= COALESCE(SUM(b.debit), 0) THEN printf('%.2f', COALESCE(SUM(b.credit), 0) - COALESCE(SUM(b.debit), 0)) ELSE '0' END as soldeCredit
FROM COMPTA_HEAD_2014 h JOIN COMPTA_BODY_2014 b
ON h.id = b.headEntry_id
JOIN COMPTA_COMPTE_PCE c
ON c.id = b.pce_id
WHERE c.num LIKE '401%'
GROUP BY c.num
UNION
SELECT '0' bigTot, '2' orderline, substr(c.num, 0, 2) as classe, substr(c.num, 0, 4) as sousclasse, '' as compte, 'TOTAL CLASSE ' || substr(c.num, 0, 2) as libCompte, COALESCE(SUM(b.debit), 0) as debitTot, COALESCE(SUM(b.credit), 0) as creditTot,
'' as soldeDebit,
'' as soldeCredit
FROM COMPTA_HEAD_2014 h JOIN COMPTA_BODY_2014 b
ON h.id = b.headEntry_id
JOIN COMPTA_COMPTE_PCE c
ON c.id = b.pce_id
WHERE c.num LIKE '401%'
GROUP BY classe
UNION
SELECT '1' bigTot, '3' orderline, substr(c.num, 0, 2) as classe, substr(c.num, 0, 4) as sousclasse, '' as compte, 'SOLDE BALANCE' as libCompte,
CASE WHEN COALESCE(SUM(b.debit), 0) >= COALESCE(SUM(b.credit), 0) THEN printf('%.2f', COALESCE(SUM(b.debit), 0) - COALESCE(SUM(b.credit), 0)) ELSE '0' END as debitTot,
CASE WHEN COALESCE(SUM(b.credit), 0) >= COALESCE(SUM(b.debit), 0) THEN printf('%.2f', COALESCE(SUM(b.credit), 0) - COALESCE(SUM(b.debit), 0)) ELSE '0' END as creditTot,
'' as soldeDebit,
'' as soldeCredit
FROM COMPTA_HEAD_2014 h JOIN COMPTA_BODY_2014 b
ON h.id = b.headEntry_id
JOIN COMPTA_COMPTE_PCE c
ON c.id = b.pce_id
WHERE c.num LIKE '401%'
GROUP BY bigTot
ORDER BY bigTot, classe, orderline |
Partager