Overview

Artifact ID: ea09920defa51df7c5722fd78c024581a2217a915cad2341596399235d7ac180
Page Name:Budget prévisionnel requête
Date: 2021-07-09 10:36:46
Original User: isabelle
Mimetype:text/x-markdown
Parent: f6d83642b02f6ba09a6b6ca4603ebc937b9ab963e3c46ce05270330e3dff849f (diff)
Next 6fae746c62e7444203f564c7bc1eaf9c6b1dfe079b1527773ce81ebd7dcfaff0
Content

Exemple de requête SQL pour l’établissement d’un suivi de budget prévisionnel. Voir aussi : le tutoriel pour son utilisation.

Mofidier .id_year != 3 et .id_year = 3 en remplaçant le numéro, ici 3, par celui de votre exercice prévisionnel (on trouve le numéro dans la barrre d’adresse du navigateur).


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

`