API tools faq
paste
Advertisement
Guest User

"Budget prévisionnel requête"

a guest
Jun 3rd, 2023
111
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 34.72 KB | Source Code | 0 0
raw download clone embed print report
  1. SELECT
  2.   "700 PRODUITS D'EXPLOITATION" AS Comptes,
  3.   CASE
  4.   WHEN (
  5.     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)
  6.   - 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
  7.   THEN '- - -'
  8.  
  9.   WHEN (
  10.     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)
  11.   - 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
  12.   THEN '+ + +'
  13.  
  14.   WHEN (
  15.     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)
  16.   - 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
  17.   THEN '= = ='
  18.   END AS 'Evolution',
  19.  
  20.   (printf("%2f%p",CAST(
  21.      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 )
  22.    || ' €' AS 'Prévisionnel',
  23.  
  24.   (printf("%2f%p",CAST(
  25.      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 )
  26.    || ' €' AS 'Année N',
  27.  
  28.   (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)
  29.    || ' €' AS 'Année N-1',
  30.  
  31.   (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)
  32.    || ' €' AS 'Année N-2'
  33.  
  34. FROM
  35.   acc_transactions at,
  36.   acc_transactions_lines atl,
  37.   acc_accounts aa
  38.  
  39. WHERE at.id = atl.id_transaction
  40.   AND atl.id_account=aa.id
  41.   AND aa.code BETWEEN 700 AND 799
  42.  
  43.  
  44. UNION
  45.  
  46.  
  47. SELECT aa.code ||
  48.   " " ||aa.label AS Comptes,
  49.   CASE
  50.  
  51.     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)
  52.    - 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
  53.   THEN '- - -'
  54.  
  55.   WHEN (
  56.     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)
  57.   - 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
  58.   THEN '+ + +'
  59.  
  60.  
  61.     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)
  62.   - 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
  63.   THEN '= = ='
  64.   END AS 'Evolution',
  65.  
  66.   (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 )
  67.    || ' €' AS 'Prévisionnel',
  68.  
  69.   (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 )
  70.    || ' €' AS 'Année N',
  71.  
  72.   (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)
  73.    || ' €'  AS 'Année N-1',
  74.  
  75.   (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)
  76.    || ' €'  AS 'Année N-2'
  77.  
  78. FROM
  79.   acc_transactions at,
  80.   acc_transactions_lines atl,
  81.   acc_accounts aa
  82.  
  83. WHERE at.id = atl.id_transaction
  84.   AND atl.id_account=aa.id
  85.   AND aa.code BETWEEN 700 AND 799 GROUP BY aa.code
  86.  
  87.  
  88. UNION
  89.  
  90.  
  91. SELECT "760 PRODUITS FINANCIERS" AS Comptes,
  92. CASE
  93. 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)
  94. - 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
  95. '- - -'
  96. 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)
  97. - 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
  98. '+ + +'
  99. 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)
  100. - 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
  101. '= = ='
  102. END
  103. AS 'Evolution',
  104. (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',
  105. (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',
  106. (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',
  107. (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'
  108. FROM acc_transactions at, acc_transactions_lines atl, acc_accounts aa
  109. WHERE at.id = atl.id_transaction
  110. AND atl.id_account=aa.id
  111. AND aa.code BETWEEN 760 AND 769
  112.  
  113.  
  114. UNION
  115.  
  116.  
  117. SELECT
  118.   "770 PRODUITS EXCEPTIONNELLES" AS Comptes,
  119.   CASE
  120.  
  121.     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)
  122.    - 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
  123.   THEN '- - -'
  124.  
  125.   WHEN (
  126.     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)
  127.   - 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
  128.   THEN '+ + +'
  129.  
  130.  
  131.     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)
  132.   - 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
  133.   THEN '= = ='
  134.   END AS 'Evolution',
  135.  
  136.  
  137.       (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',
  138.  
  139.  
  140.  
  141.      (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 )
  142.    || ' €' AS 'Année N',
  143.  
  144.   (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)
  145.    || ' €' AS 'Année N-1',
  146.  
  147.   (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)
  148.    || ' €' AS 'Année N-2'
  149.  
  150. FROM
  151.   acc_transactions at,
  152.   acc_transactions_lines atl,
  153.   acc_accounts aa
  154.  
  155. WHERE at.id = atl.id_transaction
  156.   AND atl.id_account=aa.id
  157.   AND aa.code BETWEEN 770 AND 779
  158.  
  159.  
  160. UNION
  161.  
  162.  
  163. SELECT
  164.   "600 CHARGES D'EXPLOITATION" AS Comptes,
  165.   CASE
  166.  
  167.     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)
  168.    - 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
  169.   THEN '- - -'
  170.  
  171.   WHEN (
  172.     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)
  173.   - 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
  174.   THEN '+ + +'
  175.  
  176.  
  177.     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)
  178.   - 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
  179.   THEN '= = ='
  180.   END AS 'Evolution',
  181.  
  182.  
  183.       (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',
  184.  
  185.  
  186.  
  187.      (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 )
  188.    || ' €' AS 'Année N',
  189.  
  190.   (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)
  191.    || ' €' AS 'Année N-1',
  192.  
  193.   (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)
  194.    || ' €' AS 'Année N-2'
  195.  
  196. FROM
  197.   acc_transactions at,
  198.   acc_transactions_lines atl,
  199.   acc_accounts aa
  200.  
  201. WHERE at.id = atl.id_transaction
  202.   AND atl.id_account=aa.id
  203.   AND aa.code BETWEEN 600 AND 669
  204.  
  205.  
  206. UNION
  207.  
  208.  
  209. SELECT "600 LES ACHATS" AS Comptes,
  210. CASE
  211. 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)
  212. - 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
  213. '- - -'
  214. 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)
  215. - 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
  216. '+ + +'
  217. 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)
  218. - 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
  219. '= = ='
  220. END
  221. AS 'Evolution',
  222. (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',
  223. (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',
  224. (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',
  225. (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'
  226. FROM acc_transactions at, acc_transactions_lines atl, acc_accounts aa
  227. WHERE at.id = atl.id_transaction
  228. AND atl.id_account=aa.id
  229. AND aa.code BETWEEN 600 AND 609
  230.  
  231.  
  232. UNION
  233.  
  234.  
  235. SELECT
  236.   "610 SERVICES EXTERIEURS" AS Comptes,
  237.   CASE
  238.  
  239.     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)
  240.    - 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
  241.   THEN '- - -'
  242.  
  243.   WHEN (
  244.     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)
  245.   - 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
  246.   THEN '+ + +'
  247.  
  248.  
  249.     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)
  250.   - 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
  251.   THEN '= = ='
  252.   END AS 'Evolution',
  253.  
  254.  
  255.       (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',
  256.  
  257.  
  258.  
  259.      (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 )
  260.    || ' €' AS 'Année N',
  261.  
  262.   (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)
  263.    || ' €' AS 'Année N-1',
  264.  
  265.   (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)
  266.    || ' €' AS 'Année N-2'
  267.  
  268. FROM
  269.   acc_transactions at,
  270.   acc_transactions_lines atl,
  271.   acc_accounts aa
  272.  
  273. WHERE at.id = atl.id_transaction
  274.   AND atl.id_account=aa.id
  275.   AND aa.code BETWEEN 610 AND 619
  276.  
  277.  
  278. UNION
  279.  
  280.  
  281. SELECT "620 AUTRES SERVICES EXTERIEURS" AS Comptes,
  282. CASE
  283. 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)
  284. - 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
  285. '- - -'
  286. 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)
  287. - 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
  288. '+ + +'
  289. 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)
  290. - 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
  291. '= = ='
  292. END
  293. AS 'Evolution',
  294. (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',
  295. (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',
  296. (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',
  297. (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'
  298. FROM acc_transactions at, acc_transactions_lines atl, acc_accounts aa
  299. WHERE at.id = atl.id_transaction
  300. AND atl.id_account=aa.id
  301. AND aa.code BETWEEN 620 AND 629
  302.  
  303.  
  304. UNION
  305.  
  306.  
  307. SELECT
  308.   "630 AUTRES CHARGES" AS Comptes,
  309.   CASE
  310.  
  311.     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)
  312.    - 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
  313.   THEN '- - -'
  314.  
  315.   WHEN (
  316.     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)
  317.   - 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
  318.   THEN '+ + +'
  319.  
  320.  
  321.     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)
  322.   - 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
  323.   THEN '= = ='
  324.   END AS 'Evolution',
  325.  
  326.  
  327.       (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',
  328.  
  329.  
  330.  
  331.      (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 )
  332.    || ' €' AS 'Année N',
  333.  
  334.   (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)
  335.    || ' €' AS 'Année N-1',
  336.  
  337.   (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)
  338.    || ' €' AS 'Année N-2'
  339.  
  340. FROM
  341.   acc_transactions at,
  342.   acc_transactions_lines atl,
  343.   acc_accounts aa
  344.  
  345. WHERE at.id = atl.id_transaction
  346.   AND atl.id_account=aa.id
  347.   AND aa.code BETWEEN 630 AND 659
  348.  
  349.  
  350. UNION
  351.  
  352.  
  353. SELECT "680 DOTATIONS AUX AMORTISSEMENTS" AS Comptes,
  354. CASE
  355. 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)
  356. - 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
  357. '- - -'
  358. 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)
  359. - 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
  360. '+ + +'
  361. 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)
  362. - 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
  363. '= = ='
  364. END
  365. AS 'Evolution',
  366. (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',
  367. (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',
  368. (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',
  369. (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'
  370. FROM acc_transactions at, acc_transactions_lines atl, acc_accounts aa
  371. WHERE at.id = atl.id_transaction
  372. AND atl.id_account=aa.id
  373. AND aa.code BETWEEN 680 AND 689
  374.  
  375.  
  376. UNION
  377.  
  378.  
  379. SELECT
  380.   "670 CHARGES EXCEPTIONNELLES" AS Comptes,
  381.   CASE
  382.  
  383.     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)
  384.    - 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
  385.   THEN '- - -'
  386.  
  387.   WHEN (
  388.     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)
  389.   - 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
  390.   THEN '+ + +'
  391.  
  392.  
  393.     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)
  394.   - 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
  395.   THEN '= = ='
  396.   END AS 'Evolution',
  397.  
  398.  
  399.       (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',
  400.  
  401.  
  402.  
  403.      (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 )
  404.    || ' €' AS 'Année N',
  405.  
  406.   (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)
  407.    || ' €' AS 'Année N-1',
  408.  
  409.   (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)
  410.    || ' €' AS 'Année N-2'
  411.  
  412. FROM
  413.   acc_transactions at,
  414.   acc_transactions_lines atl,
  415.   acc_accounts aa
  416.  
  417. WHERE at.id = atl.id_transaction
  418.   AND atl.id_account=aa.id
  419.   AND aa.code BETWEEN 670 AND 679
  420.  
  421.  
  422. UNION
  423.  
  424.  
  425. SELECT "660 CHARGES FINANCIERES" AS Comptes,
  426. CASE
  427. 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)
  428. - 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
  429. '- - -'
  430. 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)
  431. - 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
  432. '+ + +'
  433. 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)
  434. - 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
  435. '= = ='
  436. END
  437. AS 'Evolution',
  438. (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',
  439. (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',
  440. (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',
  441. (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'
  442. FROM acc_transactions at, acc_transactions_lines atl, acc_accounts aa
  443. WHERE at.id = atl.id_transaction
  444. AND atl.id_account=aa.id
  445. AND aa.code BETWEEN 660 AND 669
  446.  
  447.  
  448. UNION
  449.  
  450.  
  451. SELECT aa.code ||
  452.   " " ||aa.label AS Comptes,
  453.   CASE
  454.  
  455.     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)
  456.    - 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
  457.   THEN '- - -'
  458.  
  459.   WHEN (
  460.     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)
  461.   - 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
  462.   THEN '+ + +'
  463.  
  464.  
  465.     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)
  466.   - 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
  467.   THEN '= = ='
  468.   END AS 'Evolution',
  469.  
  470.  
  471.       (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',
  472.  
  473.  
  474.  
  475.      (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 )
  476.    || ' €' AS 'Année N',
  477.  
  478.   (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)
  479.    || ' €'  AS 'Année N-1',
  480.  
  481.   (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)
  482.    || ' €'  AS 'Année N-2'
  483.  
  484. FROM
  485.   acc_transactions at,
  486.   acc_transactions_lines atl,
  487.   acc_accounts aa
  488.  
  489. WHERE at.id = atl.id_transaction
  490.   AND atl.id_account=aa.id
  491.   AND aa.code BETWEEN 600 AND 699 GROUP BY aa.code
  492.  
  493.  
  494. UNION
  495.  
  496.  
  497. SELECT "TOTAL DES PRODUITS" AS Comptes,
  498. CASE
  499. 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)
  500. - 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
  501. '- - -'
  502. 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)
  503. - 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
  504. '+ + +'
  505. 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)
  506. - 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
  507. '= = ='
  508. END
  509. AS 'Evolution',
  510. (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',
  511. (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',
  512. (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',
  513. (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'
  514. FROM acc_transactions at, acc_transactions_lines atl, acc_accounts aa
  515. WHERE at.id = atl.id_transaction
  516. AND atl.id_account=aa.id
  517. AND aa.code BETWEEN 700 AND 799
  518.  
  519.  
  520. UNION
  521.  
  522.  
  523. SELECT
  524.   "TOTAL DES CHARGES" AS Comptes,
  525.   CASE
  526.  
  527.     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)
  528.    - 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
  529.   THEN '- - -'
  530.  
  531.   WHEN (
  532.     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)
  533.   - 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
  534.   THEN '+ + +'
  535.  
  536.  
  537.     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)
  538.   - 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
  539.   THEN '= = ='
  540.   END AS 'Evolution',
  541.  
  542.  
  543.       (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',
  544.  
  545.  
  546.  
  547.      (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 )
  548.    || ' €' AS 'Année N',
  549.  
  550.   (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)
  551.    || ' €' AS 'Année N-1',
  552.  
  553.   (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)
  554.    || ' €' AS 'Année N-2'
  555.  
  556. FROM
  557.   acc_transactions at,
  558.   acc_transactions_lines atl,
  559.   acc_accounts aa
  560.  
  561. WHERE at.id = atl.id_transaction
  562.   AND atl.id_account=aa.id
  563.   AND aa.code BETWEEN 600 AND 699
  564.  
  565.  
  566. UNION
  567.  
  568.  
  569. SELECT "TOTAL DU COMPTE DE RESULTAT ( < 0 : Déficit, > 0 : Excédent)" AS Comptes,
  570. CASE
  571. 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)
  572. - 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
  573. '- - -'
  574. 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)
  575. - 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
  576. '+ + +'
  577. 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)
  578. - 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
  579. '= = ='
  580. END
  581. AS 'Evolution',
  582. (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',
  583. (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',
  584. (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',
  585. (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'
  586. FROM acc_transactions at, acc_transactions_lines atl, acc_accounts aa
  587. WHERE at.id = atl.id_transaction
  588. AND atl.id_account=aa.id
  589. AND aa.code BETWEEN 600 AND 799
Tags: sql horror
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!