Bonjour @ tous,
Si cela peut aider certains, voici une requête pour avoir le compte de
résultat sur 3 ans en arrière avec des sous-rubriques. Je pense qu'elle
peut être perfectible.
SELECT "700 PRODUITS D'EXPLOITATION" as Comptes,
(printf("%2f%p",CAST(SUM(CASE WHEN CAST(strftime('%Y',
date('now')) -
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,
(printf("%2f%p",CAST(SUM(CASE WHEN CAST(strftime('%Y',
date('now')) -
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,
(printf("%2f%p",CAST(SUM(CASE WHEN CAST(strftime('%Y',
date('now')) -
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,
(printf("%2f%p",CAST(SUM(CASE WHEN CAST(strftime('%Y',
date('now')) -
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,
(printf("%2f%p",CAST(SUM(CASE WHEN CAST(strftime('%Y',
date('now')) -
strftime('%Y', at.date) AS INT) == 0 THEN 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,
(printf("%2f%p",CAST(SUM(CASE WHEN CAST(strftime('%Y',
date('now')) -
strftime('%Y', at.date) AS INT) == 0 THEN 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,
(printf("%2f%p",CAST(SUM(CASE WHEN CAST(strftime('%Y',
date('now')) -
strftime('%Y', at.date) AS INT) == 0 THEN 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,
(printf("%2f%p",CAST(SUM(CASE WHEN CAST(strftime('%Y',
date('now')) -
strftime('%Y', at.date) AS INT) == 0 THEN 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,
(printf("%2f%p",CAST(SUM(CASE WHEN CAST(strftime('%Y',
date('now')) -
strftime('%Y', at.date) AS INT) == 0 THEN 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,
(printf("%2f%p",CAST(SUM(CASE WHEN CAST(strftime('%Y',
date('now')) -
strftime('%Y', at.date) AS INT) == 0 THEN 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,
(printf("%2f%p",CAST(SUM(CASE WHEN CAST(strftime('%Y',
date('now')) -
strftime('%Y', at.date) AS INT) == 0 THEN 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,
(printf("%2f%p",CAST(SUM(CASE WHEN CAST(strftime('%Y',
date('now')) -
strftime('%Y', at.date) AS INT) == 0 THEN 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,
(printf("%2f%p",CAST(SUM(CASE WHEN CAST(strftime('%Y',
date('now')) -
strftime('%Y', at.date) AS INT) == 0 THEN 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,
(printf("%2f%p",CAST(SUM(CASE WHEN CAST(strftime('%Y',
date('now')) -
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,
(printf("%2f%p",CAST(SUM(CASE WHEN CAST(strftime('%Y',
date('now')) -
strftime('%Y', at.date) AS INT) == 0 THEN 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,
(printf("%2f%p",CAST(SUM(CASE WHEN CAST(strftime('%Y',
date('now')) -
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
*François WEINACKER*
*CHOR'HUs Rouen Normandie <http://www.chorhus-rn.fr>*
*Carillon de la Cathédrale de Rouen <http://www.carillon-rouen.fr>*
*Art & Culture du Diocèse de Rouen
<https://rouen.catholique.fr/diocese/services/culture-2/>*