Outils pour utilisateurs
Panneau latéral
paheko:sql:compta
Compta
Compte de résultat sur 3 ans
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
paheko/sql/compta.txt · Dernière modification: 2024/03/08 12:14 de jce
