Overview

Artifact ID: e73710c03812e61e181b6adb2db1d5cd156362add8051ad88b295cf522373aef
Page Name:Budget prévisionnel requête
Date: 2021-07-09 10:24:18
Original User: isabelle
Mimetype:text/x-markdown
Next 0441fb5723554c4348a35be0cda370f0113cc9dd5e3a3bc6a698cf774538322a
Content

Exemple de requête SQL pour l’établissement d’un suivi de budget prévisionnel

``` sql/Mofidier .id_year != 3 et .id_year = 3 en remplaçant le numéro, ici 3, par celui de votre exercice prévisionnel/

SELECT "700 PRODUITS D'EXPLOITATION" as Comptes, CASE WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END)) < 0 THEN '- - -' WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END)) > 0 THEN '+ + +' WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END)) = 0 THEN '= = =' END as 'Evolution', (printf("%2f%p",CAST(SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END) as INT))/100 )|| ' €' as 'Prévisionnel', (printf("%2f%p",CAST(SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END) as INT))/100 )|| ' €' as 'Année N', (printf("%2f%p",SUM(CASE WHEN CAST(strftime('%Y', date('now', 'start of year', '-1 year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END))/100) || ' €' as 'Année N-1', (printf("%2f%p",SUM(CASE WHEN CAST(strftime('%Y', date('now', 'start of year', '-2 year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END))/100) || ' €' as 'Année N-2' FROM acc_transactions at, acc_transactions_lines atl, acc_accounts aa WHERE at.id = atl.id_transaction AND atl.id_account=aa.id AND aa.code BETWEEN 700 AND 799 UNION SELECT aa.code || " " ||aa.label as Comptes, CASE WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END)) < 0 THEN '- - -' WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END)) > 0 THEN '+ + +' WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END)) = 0 THEN '= = =' END as 'Evolution', (printf("%2f%p",CAST(SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END) as INT))/100 )|| ' €' as 'Prévisionnel', (printf("%2f%p",CAST(SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END) as INT))/100 )|| ' €' as 'Année N', (printf("%2f%p",SUM(CASE WHEN CAST(strftime('%Y', date('now', 'start of year', '-1 year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END))/100) || ' €' as 'Année N-1', (printf("%2f%p",SUM(CASE WHEN CAST(strftime('%Y', date('now', 'start of year', '-2 year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END))/100) || ' €' as 'Année N-2' FROM acc_transactions at, acc_transactions_lines atl, acc_accounts aa WHERE at.id = atl.id_transaction AND atl.id_account=aa.id AND aa.code BETWEEN 700 AND 799 GROUP BY aa.code UNION SELECT "760 PRODUITS FINANCIERS" as Comptes, CASE WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END)) < 0 THEN '- - -' WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END)) > 0 THEN '+ + +' WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END)) = 0 THEN '= = =' END as 'Evolution', (printf("%2f%p",CAST(SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END) as INT))/100 )|| ' €' as 'Prévisionnel', (printf("%2f%p",CAST(SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END) as INT))/100 )|| ' €' as 'Année N', (printf("%2f%p",SUM(CASE WHEN CAST(strftime('%Y', date('now', 'start of year', '-1 year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END))/100) || ' €' as 'Année N-1', (printf("%2f%p",SUM(CASE WHEN CAST(strftime('%Y', date('now', 'start of year', '-2 year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END))/100) || ' €' as 'Année N-2' FROM acc_transactions at, acc_transactions_lines atl, acc_accounts aa WHERE at.id = atl.id_transaction AND atl.id_account=aa.id AND aa.code BETWEEN 760 AND 769 UNION SELECT "770 PRODUITS EXCEPTIONNELLES" as Comptes, CASE WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END)) < 0 THEN '- - -' WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END)) > 0 THEN '+ + +' WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END)) = 0 THEN '= = =' END as 'Evolution', (printf("%2f%p",CAST(SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END) as INT))/100 )|| ' €' as 'Prévisionnel', (printf("%2f%p",CAST(SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END) as INT))/100 )|| ' €' as 'Année N', (printf("%2f%p",SUM(CASE WHEN CAST(strftime('%Y', date('now', 'start of year', '-1 year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END))/100) || ' €' as 'Année N-1', (printf("%2f%p",SUM(CASE WHEN CAST(strftime('%Y', date('now', 'start of year', '-2 year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END))/100) || ' €' as 'Année N-2' FROM acc_transactions at, acc_transactions_lines atl, acc_accounts aa WHERE at.id = atl.id_transaction AND atl.id_account=aa.id AND aa.code BETWEEN 770 AND 779

UNION SELECT "600 CHARGES D'EXPLOITATION" as Comptes, CASE WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END)) < 0 THEN '- - -' WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END)) > 0 THEN '+ + +' WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END)) = 0 THEN '= = =' END as 'Evolution', (printf("%2f%p",CAST(SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END) as INT))/100 )|| ' €' as 'Prévisionnel', (printf("%2f%p",CAST(SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END) as INT))/100 )|| ' €' as 'Année N', (printf("%2f%p",SUM(CASE WHEN CAST(strftime('%Y', date('now', 'start of year', '-1 year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.debit ELSE 0 END))/100) || ' €' as 'Année N-1', (printf("%2f%p",SUM(CASE WHEN CAST(strftime('%Y', date('now', 'start of year', '-2 year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.debit ELSE 0 END))/100) || ' €' as 'Année N-2' FROM acc_transactions at, acc_transactions_lines atl, acc_accounts aa WHERE at.id = atl.id_transaction AND atl.id_account=aa.id AND aa.code BETWEEN 600 AND 669 UNION SELECT "600 LES ACHATS" as Comptes, CASE WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END)) < 0 THEN '- - -' WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END)) > 0 THEN '+ + +' WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END)) = 0 THEN '= = =' END as 'Evolution', (printf("%2f%p",CAST(SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END) as INT))/100 )|| ' €' as 'Prévisionnel', (printf("%2f%p",CAST(SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END) as INT))/100 )|| ' €' as 'Année N', (printf("%2f%p",SUM(CASE WHEN CAST(strftime('%Y', date('now', 'start of year', '-1 year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.debit ELSE 0 END))/100) || ' €' as 'Année N-1', (printf("%2f%p",SUM(CASE WHEN CAST(strftime('%Y', date('now', 'start of year', '-2 year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.debit ELSE 0 END))/100) || ' €' as 'Année N-2' FROM acc_transactions at, acc_transactions_lines atl, acc_accounts aa WHERE at.id = atl.id_transaction AND atl.id_account=aa.id AND aa.code BETWEEN 600 AND 609 UNION SELECT "610 SERVICES EXTERIEURS" as Comptes, CASE WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END)) < 0 THEN '- - -' WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END)) > 0 THEN '+ + +' WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END)) = 0 THEN '= = =' END as 'Evolution', (printf("%2f%p",CAST(SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END) as INT))/100 )|| ' €' as 'Prévisionnel', (printf("%2f%p",CAST(SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END) as INT))/100 )|| ' €' as 'Année N', (printf("%2f%p",SUM(CASE WHEN CAST(strftime('%Y', date('now', 'start of year', '-1 year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.debit ELSE 0 END))/100) || ' €' as 'Année N-1', (printf("%2f%p",SUM(CASE WHEN CAST(strftime('%Y', date('now', 'start of year', '-2 year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.debit ELSE 0 END))/100) || ' €' as 'Année N-2' FROM acc_transactions at, acc_transactions_lines atl, acc_accounts aa WHERE at.id = atl.id_transaction AND atl.id_account=aa.id AND aa.code BETWEEN 610 AND 619 UNION SELECT "620 AUTRES SERVICES EXTERIEURS" as Comptes, CASE WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END)) < 0 THEN '- - -' WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END)) > 0 THEN '+ + +' WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END)) = 0 THEN '= = =' END as 'Evolution', (printf("%2f%p",CAST(SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END) as INT))/100 )|| ' €' as 'Prévisionnel', (printf("%2f%p",CAST(SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END) as INT))/100 )|| ' €' as 'Année N', (printf("%2f%p",SUM(CASE WHEN CAST(strftime('%Y', date('now', 'start of year', '-1 year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.debit ELSE 0 END))/100) || ' €' as 'Année N-1', (printf("%2f%p",SUM(CASE WHEN CAST(strftime('%Y', date('now', 'start of year', '-2 year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.debit ELSE 0 END))/100) || ' €' as 'Année N-2' FROM acc_transactions at, acc_transactions_lines atl, acc_accounts aa WHERE at.id = atl.id_transaction AND atl.id_account=aa.id AND aa.code BETWEEN 620 AND 629 UNION SELECT "630 AUTRES CHARGES" as Comptes, CASE WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END)) < 0 THEN '- - -' WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END)) > 0 THEN '+ + +' WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END)) = 0 THEN '= = =' END as 'Evolution', (printf("%2f%p",CAST(SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END) as INT))/100 )|| ' €' as 'Prévisionnel', (printf("%2f%p",CAST(SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END) as INT))/100 )|| ' €' as 'Année N', (printf("%2f%p",SUM(CASE WHEN CAST(strftime('%Y', date('now', 'start of year', '-1 year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.debit ELSE 0 END))/100) || ' €' as 'Année N-1', (printf("%2f%p",SUM(CASE WHEN CAST(strftime('%Y', date('now', 'start of year', '-2 year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.debit ELSE 0 END))/100) || ' €' as 'Année N-2' FROM acc_transactions at, acc_transactions_lines atl, acc_accounts aa WHERE at.id = atl.id_transaction AND atl.id_account=aa.id AND aa.code BETWEEN 630 AND 659 UNION SELECT "680 DOTATIONS AUX AMORTISSEMENTS" as Comptes, CASE WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END)) < 0 THEN '- - -' WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END)) > 0 THEN '+ + +' WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END)) = 0 THEN '= = =' END as 'Evolution', (printf("%2f%p",CAST(SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END) as INT))/100 )|| ' €' as 'Prévisionnel', (printf("%2f%p",CAST(SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END) as INT))/100 )|| ' €' as 'Année N', (printf("%2f%p",SUM(CASE WHEN CAST(strftime('%Y', date('now', 'start of year', '-1 year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.debit ELSE 0 END))/100) || ' €' as 'Année N-1', (printf("%2f%p",SUM(CASE WHEN CAST(strftime('%Y', date('now', 'start of year', '-2 year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.debit ELSE 0 END))/100) || ' €' as 'Année N-2' FROM acc_transactions at, acc_transactions_lines atl, acc_accounts aa WHERE at.id = atl.id_transaction AND atl.id_account=aa.id AND aa.code BETWEEN 680 AND 689 UNION SELECT "670 CHARGES EXCEPTIONNELLES" as Comptes, CASE WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END)) < 0 THEN '- - -' WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END)) > 0 THEN '+ + +' WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END)) = 0 THEN '= = =' END as 'Evolution', (printf("%2f%p",CAST(SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END) as INT))/100 )|| ' €' as 'Prévisionnel', (printf("%2f%p",CAST(SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END) as INT))/100 )|| ' €' as 'Année N', (printf("%2f%p",SUM(CASE WHEN CAST(strftime('%Y', date('now', 'start of year', '-1 year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.debit ELSE 0 END))/100) || ' €' as 'Année N-1', (printf("%2f%p",SUM(CASE WHEN CAST(strftime('%Y', date('now', 'start of year', '-2 year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.debit ELSE 0 END))/100) || ' €' as 'Année N-2' FROM acc_transactions at, acc_transactions_lines atl, acc_accounts aa WHERE at.id = atl.id_transaction AND atl.id_account=aa.id AND aa.code BETWEEN 670 AND 679 UNION SELECT "660 CHARGES FINANCIERES" as Comptes, CASE WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END)) < 0 THEN '- - -' WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END)) > 0 THEN '+ + +' WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END)) = 0 THEN '= = =' END as 'Evolution', (printf("%2f%p",CAST(SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END) as INT))/100 )|| ' €' as 'Prévisionnel', (printf("%2f%p",CAST(SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END) as INT))/100 )|| ' €' as 'Année N', (printf("%2f%p",SUM(CASE WHEN CAST(strftime('%Y', date('now', 'start of year', '-1 year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.debit ELSE 0 END))/100) || ' €' as 'Année N-1', (printf("%2f%p",SUM(CASE WHEN CAST(strftime('%Y', date('now', 'start of year', '-2 year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.debit ELSE 0 END))/100) || ' €' as 'Année N-2' FROM acc_transactions at, acc_transactions_lines atl, acc_accounts aa WHERE at.id = atl.id_transaction AND atl.id_account=aa.id AND aa.code BETWEEN 660 AND 669 UNION SELECT aa.code || " " ||aa.label as Comptes, CASE WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END)) < 0 THEN '- - -' WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END)) > 0 THEN '+ + +' WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END)) = 0 THEN '= = =' END as 'Evolution', (printf("%2f%p",CAST(SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END) as INT))/100 )|| ' €' as 'Prévisionnel', (printf("%2f%p",CAST(SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN -atl.credit+atl.debit ELSE 0 END) as INT))/100 )|| ' €' as 'Année N', (printf("%2f%p",SUM(CASE WHEN CAST(strftime('%Y', date('now', 'start of year', '-1 year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.debit ELSE 0 END))/100) || ' €' as 'Année N-1', (printf("%2f%p",SUM(CASE WHEN CAST(strftime('%Y', date('now', 'start of year', '-2 year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.debit ELSE 0 END))/100) || ' €' as 'Année N-2' FROM acc_transactions at, acc_transactions_lines atl, acc_accounts aa WHERE at.id = atl.id_transaction AND atl.id_account=aa.id AND aa.code BETWEEN 600 AND 699 GROUP BY aa.code UNION SELECT "TOTAL DES PRODUITS" as Comptes, CASE WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year', '-1 year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END)) < 0 THEN '- - -' WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year', '-1 year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END)) > 0 THEN '+ + +' WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year', '-1 year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END)) = 0 THEN '= = =' END as 'Evolution', (printf("%2f%p",CAST(SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END) as INT))/100 )|| ' €' as 'Prévisionnel', (printf("%2f%p",CAST(SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END) as INT))/100 )|| ' €' as 'Année N', (printf("%2f%p",SUM(CASE WHEN CAST(strftime('%Y', date('now', 'start of year', '-1 year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END))/100) || ' €' as 'Année N-1', (printf("%2f%p",SUM(CASE WHEN CAST(strftime('%Y', date('now', 'start of year', '-2 year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END))/100) || ' €' as 'Année N-2' FROM acc_transactions at, acc_transactions_lines atl, acc_accounts aa WHERE at.id = atl.id_transaction AND atl.id_account=aa.id AND aa.code BETWEEN 700 AND 799 UNION SELECT "TOTAL DES CHARGES" as Comptes, CASE WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year', '-1 year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END)) < 0 THEN '- - -' WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year', '-1 year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END)) > 0 THEN '+ + +' WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year', '-1 year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END)) = 0 THEN '= = =' END as 'Evolution', (printf("%2f%p",CAST(SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END) as INT))/100 )|| ' €' as 'Prévisionnel', (printf("%2f%p",CAST(SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END) as INT))/100 )|| ' €' as 'Année N', (printf("%2f%p",SUM(CASE WHEN CAST(strftime('%Y', date('now', 'start of year', '-1 year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.debit ELSE 0 END))/100) || ' €' as 'Année N-1', (printf("%2f%p",SUM(CASE WHEN CAST(strftime('%Y', date('now', 'start of year', '-2 year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.debit ELSE 0 END))/100) || ' €' as 'Année N-2' FROM acc_transactions at, acc_transactions_lines atl, acc_accounts aa WHERE at.id = atl.id_transaction AND atl.id_account=aa.id AND aa.code BETWEEN 600 AND 699 UNION SELECT "TOTAL DU COMPTE DE RESULTAT ( < 0 : Déficit, > 0 : Excédent)" as Comptes, CASE WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year', '-1 year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END)) < 0 THEN '- - -' WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year', '-1 year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END)) > 0 THEN '+ + +' WHEN (SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END) - SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year', '-1 year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END)) = 0 THEN '= = =' END as 'Evolution', (printf("%2f%p",CAST(SUM(CASE WHEN AT.id_year = 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END) as INT))/100 )|| ' €' as 'Prévisionnel', (printf("%2f%p",CAST(SUM(CASE WHEN AT.id_year != 3 AND CAST(strftime('%Y', date('now', 'start of year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END) as INT))/100 )|| ' €' as 'Année N', (printf("%2f%p",SUM(CASE WHEN CAST(strftime('%Y', date('now', 'start of year', '-1 year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END))/100) || ' €' as 'Année N-1', (printf("%2f%p",SUM(CASE WHEN CAST(strftime('%Y', date('now', 'start of year', '-2 year')) - strftime('%Y', at.date) AS INT) == 0 THEN atl.credit-atl.debit ELSE 0 END))/100) || ' €' as 'Année N-2' FROM acc_transactions at, acc_transactions_lines atl, acc_accounts aa WHERE at.id = atl.id_transaction AND atl.id_account=aa.id AND aa.code BETWEEN 600 AND 799