~~NOCACHE~~
====== Trésorerie - Dépenses et recettes par mois 2020 ======
<WRAP center round box cyan>

En complément des [[wiki:finances:comptes_2020|]],vous trouverez ci-dessous un tableau des recettes/dépenses en date de valeur par mois d'exercice.\\

Ces tableaux donnent des indications pour évaluer et mettre à jour un [[wiki:finances:tresorerie_2020|tableau de trésorerie]].
Ils présentent par catégorie et par mois le nombre de transactions, le montant total du mois et les montants moyen, minimum et maximum de transaction.
</WRAP>

===== Décembre =====
<sql>
 SELECT 
cat.label as 'Catégorie',
MONTH(trans.datev) as 'Mois',
COUNT(trans.rowid) as 'Nb mvt',
FORMAT(SUM(trans.amount),2,'fr-FR') as 'Total',
FORMAT(AVG(trans.amount),2,'fr-FR') as 'Moyenne',
FORMAT(MIN(trans.amount),2,'fr-FR') as 'Minimum',
FORMAT(MAX(trans.amount),2,'fr-FR') as 'Maximum'

FROM llx89_bank_categ as cat
LEFT JOIN llx89_bank_class as class on class.fk_categ = cat.rowid 
JOIN llx89_bank as trans on class.lineid = trans.rowid

WHERE 
  (cat.label like '%Recette%' OR cat.label like '%Dépense%')
AND trans.datev > '2019-12-31'
AND trans.datev < '2021-01-01'
AND MONTH(trans.datev) = 12
GROUP by cat.label, Mois
</sql>


===== Novembre =====
<sql>
 SELECT 
cat.label as 'Catégorie',
MONTH(trans.datev) as 'Mois',
COUNT(trans.rowid) as 'Nb mvt',
FORMAT(SUM(trans.amount),2,'fr-FR') as 'Total',
FORMAT(AVG(trans.amount),2,'fr-FR') as 'Moyenne',
FORMAT(MIN(trans.amount),2,'fr-FR') as 'Minimum',
FORMAT(MAX(trans.amount),2,'fr-FR') as 'Maximum'

FROM llx89_bank_categ as cat
LEFT JOIN llx89_bank_class as class on class.fk_categ = cat.rowid 
JOIN llx89_bank as trans on class.lineid = trans.rowid

WHERE 
  (cat.label like '%Recette%' OR cat.label like '%Dépense%')
AND trans.datev > '2019-12-31'
AND trans.datev < '2021-01-01'
AND MONTH(trans.datev) = 11
GROUP by cat.label, Mois
</sql>


===== Octobre =====
<sql>
 SELECT 
cat.label as 'Catégorie',
MONTH(trans.datev) as 'Mois',
COUNT(trans.rowid) as 'Nb mvt',
FORMAT(SUM(trans.amount),2,'fr-FR') as 'Total',
FORMAT(AVG(trans.amount),2,'fr-FR') as 'Moyenne',
FORMAT(MIN(trans.amount),2,'fr-FR') as 'Minimum',
FORMAT(MAX(trans.amount),2,'fr-FR') as 'Maximum'

FROM llx89_bank_categ as cat
LEFT JOIN llx89_bank_class as class on class.fk_categ = cat.rowid 
JOIN llx89_bank as trans on class.lineid = trans.rowid

WHERE 
  (cat.label like '%Recette%' OR cat.label like '%Dépense%')
AND trans.datev > '2019-12-31'
AND trans.datev < '2021-01-01'
AND MONTH(trans.datev) = 10
GROUP by cat.label, Mois
</sql>





===== Septembre =====
<sql>
 SELECT 
cat.label as 'Catégorie',
MONTH(trans.datev) as 'Mois',
COUNT(trans.rowid) as 'Nb mvt',
FORMAT(SUM(trans.amount),2,'fr-FR') as 'Total',
FORMAT(AVG(trans.amount),2,'fr-FR') as 'Moyenne',
FORMAT(MIN(trans.amount),2,'fr-FR') as 'Minimum',
FORMAT(MAX(trans.amount),2,'fr-FR') as 'Maximum'

FROM llx89_bank_categ as cat
LEFT JOIN llx89_bank_class as class on class.fk_categ = cat.rowid 
JOIN llx89_bank as trans on class.lineid = trans.rowid

WHERE 
  (cat.label like '%Recette%' OR cat.label like '%Dépense%')
AND trans.datev > '2019-12-31'
AND trans.datev < '2021-01-01'
AND MONTH(trans.datev) = 9
GROUP by cat.label, Mois
</sql>


===== Août =====
<sql>
 SELECT 
cat.label as 'Catégorie',
MONTH(trans.datev) as 'Mois',
COUNT(trans.rowid) as 'Nb mvt',
FORMAT(SUM(trans.amount),2,'fr-FR') as 'Total',
FORMAT(AVG(trans.amount),2,'fr-FR') as 'Moyenne',
FORMAT(MIN(trans.amount),2,'fr-FR') as 'Minimum',
FORMAT(MAX(trans.amount),2,'fr-FR') as 'Maximum'

FROM llx89_bank_categ as cat
LEFT JOIN llx89_bank_class as class on class.fk_categ = cat.rowid 
JOIN llx89_bank as trans on class.lineid = trans.rowid

WHERE 
  (cat.label like '%Recette%' OR cat.label like '%Dépense%')
AND trans.datev > '2019-12-31'
AND trans.datev < '2021-01-01'
AND MONTH(trans.datev) = 8
GROUP by cat.label, Mois
</sql>


===== juillet =====
<sql>
 SELECT 
cat.label as 'Catégorie',
MONTH(trans.datev) as 'Mois',
COUNT(trans.rowid) as 'Nb mvt',
FORMAT(SUM(trans.amount),2,'fr-FR') as 'Total',
FORMAT(AVG(trans.amount),2,'fr-FR') as 'Moyenne',
FORMAT(MIN(trans.amount),2,'fr-FR') as 'Minimum',
FORMAT(MAX(trans.amount),2,'fr-FR') as 'Maximum'

FROM llx89_bank_categ as cat
LEFT JOIN llx89_bank_class as class on class.fk_categ = cat.rowid 
JOIN llx89_bank as trans on class.lineid = trans.rowid

WHERE 
  (cat.label like '%Recette%' OR cat.label like '%Dépense%')
AND trans.datev > '2019-12-31'
AND trans.datev < '2021-01-01'
AND MONTH(trans.datev) = 7
GROUP by cat.label, Mois
</sql>



===== juin =====
<sql>
 SELECT 
cat.label as 'Catégorie',
MONTH(trans.datev) as 'Mois',
COUNT(trans.rowid) as 'Nb mvt',
FORMAT(SUM(trans.amount),2,'fr-FR') as 'Total',
FORMAT(AVG(trans.amount),2,'fr-FR') as 'Moyenne',
FORMAT(MIN(trans.amount),2,'fr-FR') as 'Minimum',
FORMAT(MAX(trans.amount),2,'fr-FR') as 'Maximum'

FROM llx89_bank_categ as cat
LEFT JOIN llx89_bank_class as class on class.fk_categ = cat.rowid 
JOIN llx89_bank as trans on class.lineid = trans.rowid

WHERE 
  (cat.label like '%Recette%' OR cat.label like '%Dépense%')
AND trans.datev > '2019-12-31'
AND trans.datev < '2021-01-01'
AND MONTH(trans.datev) = 6
GROUP by cat.label, Mois
</sql>

===== mai =====
<sql>
 SELECT 
cat.label as 'Catégorie',
MONTH(trans.datev) as 'Mois',
COUNT(trans.rowid) as 'Nb mvt',
FORMAT(SUM(trans.amount),2,'fr-FR') as 'Total',
FORMAT(AVG(trans.amount),2,'fr-FR') as 'Moyenne',
FORMAT(MIN(trans.amount),2,'fr-FR') as 'Minimum',
FORMAT(MAX(trans.amount),2,'fr-FR') as 'Maximum'

FROM llx89_bank_categ as cat
LEFT JOIN llx89_bank_class as class on class.fk_categ = cat.rowid 
JOIN llx89_bank as trans on class.lineid = trans.rowid

WHERE 
  (cat.label like '%Recette%' OR cat.label like '%Dépense%')
AND trans.datev > '2019-12-31'
AND trans.datev < '2021-01-01'
AND MONTH(trans.datev) = 5
GROUP by cat.label, Mois
</sql>

===== avril =====
<sql>
 SELECT 
cat.label as 'Catégorie',
MONTH(trans.datev) as 'Mois',
COUNT(trans.rowid) as 'Nb mvt',
FORMAT(SUM(trans.amount),2,'fr-FR') as 'Total',
FORMAT(AVG(trans.amount),2,'fr-FR') as 'Moyenne',
FORMAT(MIN(trans.amount),2,'fr-FR') as 'Minimum',
FORMAT(MAX(trans.amount),2,'fr-FR') as 'Maximum'

FROM llx89_bank_categ as cat
LEFT JOIN llx89_bank_class as class on class.fk_categ = cat.rowid 
JOIN llx89_bank as trans on class.lineid = trans.rowid

WHERE 
  (cat.label like '%Recette%' OR cat.label like '%Dépense%')
AND trans.datev > '2019-12-31'
AND trans.datev < '2021-01-01'
AND MONTH(trans.datev) = 4
GROUP by cat.label, Mois
</sql>

===== mars =====
<sql>
 SELECT 
cat.label as 'Catégorie',
MONTH(trans.datev) as 'Mois',
COUNT(trans.rowid) as 'Nb mvt',
FORMAT(SUM(trans.amount),2,'fr-FR') as 'Total',
FORMAT(AVG(trans.amount),2,'fr-FR') as 'Moyenne',
FORMAT(MIN(trans.amount),2,'fr-FR') as 'Minimum',
FORMAT(MAX(trans.amount),2,'fr-FR') as 'Maximum'

FROM llx89_bank_categ as cat
LEFT JOIN llx89_bank_class as class on class.fk_categ = cat.rowid 
JOIN llx89_bank as trans on class.lineid = trans.rowid

WHERE 
  (cat.label like '%Recette%' OR cat.label like '%Dépense%')
AND trans.datev > '2019-12-31'
AND trans.datev < '2021-01-01'
AND MONTH(trans.datev) = 3
GROUP by cat.label, Mois
</sql>
===== février =====
<sql>
 SELECT 
cat.label as 'Catégorie',
MONTH(trans.datev) as 'Mois',
COUNT(trans.rowid) as 'Nb mvt',
FORMAT(SUM(trans.amount),2,'fr-FR') as 'Total',
FORMAT(AVG(trans.amount),2,'fr-FR') as 'Moyenne',
FORMAT(MIN(trans.amount),2,'fr-FR') as 'Minimum',
FORMAT(MAX(trans.amount),2,'fr-FR') as 'Maximum'

FROM llx89_bank_categ as cat
LEFT JOIN llx89_bank_class as class on class.fk_categ = cat.rowid 
JOIN llx89_bank as trans on class.lineid = trans.rowid

WHERE 
  (cat.label like '%Recette%' OR cat.label like '%Dépense%')
AND trans.datev > '2019-12-31'
AND trans.datev < '2021-01-01'
AND MONTH(trans.datev) = 2
GROUP by cat.label, Mois
</sql>
===== janvier =====
<sql>
 SELECT 
cat.label as 'Catégorie',
MONTH(trans.datev) as 'Mois',
COUNT(trans.rowid) as 'Nb mvt',
FORMAT(SUM(trans.amount),2,'fr-FR') as 'Total',
FORMAT(AVG(trans.amount),2,'fr-FR') as 'Moyenne',
FORMAT(MIN(trans.amount),2,'fr-FR') as 'Minimum',
FORMAT(MAX(trans.amount),2,'fr-FR') as 'Maximum'

FROM llx89_bank_categ as cat
LEFT JOIN llx89_bank_class as class on class.fk_categ = cat.rowid 
JOIN llx89_bank as trans on class.lineid = trans.rowid

WHERE 
  (cat.label like '%Recette%' OR cat.label like '%Dépense%')
AND trans.datev > '2019-12-31'
AND trans.datev < '2021-01-01'
AND MONTH(trans.datev) = 1
GROUP by cat.label, Mois
</sql>



~~SNIPPET_O1578320292~~wiki:snippets:retourner_aux_sommaires~~
/* Bloc à utiliser au niveau R+3 */
====== Retourner aux sommaires ======
<WRAP center centeralign>
/* lien vers les sommaires des catégories supérieure */
[[:]] - [[..:]] - [[.:]]
</WRAP>
~~SNIPPET_C~~wiki:snippets:retourner_aux_sommaires~~