Discussion :

[jerome1980]

Bonjour

Je viens vers vous dans l'espoir d'un peut d'aide pour établir une requête SQL (mes compétences en dev sont très limités malheureusement)

Je travail sur un petit tableau pour administrer la comptabilité de ma micro entreprise.
Je fais appel au plugin wpDataTables pour wordpress qui s'occupe de la mise en forme de tableaux dynamiques.

Je dispose d'une table 'factures' avec les champs suivants :

ID	num	projet	montant	date_fact	date_encaiss
Auto	n° facture	nom du projet	montant facturé	date facturation	date encaissement

Je cherche a lister dans un autre tableau le montant total des factures encaissées et non encaissé par année.
Ce qui pourrait ressembler à ça :

ID	annee	montant_fact	montant_encaiss
1	2016	10000	10000
2	2017	15000	15000
3	2018	20000	15000

Pour m'en approcher j'arrive à monter cette requête qui intègre que les factures encaissés :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT ID, YEAR(factures.`date_encaiss`) AS annee, SUM(montant) AS montant_encaiss
FROM factures
WHERE date_encaiss IS NOT NULL
GROUP BY Annee

Selon vous est-il possible de construire une requète SQL pour afficher les résultats (année + montant facturé + montant encaissé) sans avoir à créer une table intermédiaire ?

Merci par avance pour votre aide !

[al1_24]

Il suffit juste d'ajouter un CASE :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT   YEAR(COALESCE(date_encaiss, date_fact))   AS annee
      ,  SUM(montant)                              AS montant_fact
      ,  SUM(CASE WHEN date_encaiss IS NOT NULL THEN montant END) AS montant_encaiss
FROM     factures
GROUP BY Annee

Au passage, ton modèle de données ne prévoit pas les réglements partiels ou en plusieurs fois.

[jerome1980]

Il suffit juste d'ajouter un CASE :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT   YEAR(COALESCE(date_encaiss, date_fact))   AS annee
      ,  SUM(montant)                              AS montant_fact
      ,  SUM(CASE WHEN date_encaiss IS NOT NULL THEN montant END) AS montant_encaiss
FROM     factures
GROUP BY Annee

Au passage, ton modèle de données ne prévoit pas les réglements partiels ou en plusieurs fois.

Merci beaucoup al1 ! ça fonctionne
Je vais regarder la doc SQL sur la fonction CASE pour bien comprendre le fonctionnement

Pour ce qui est des règlement partiel.. c'est un peut trop hard pour moi a mettre en place ^^
On va faire simple

Ce qui serait cool maintenant c'est que j'arrive a faire la même chose avec les données mensuel, dans un tableau avec les mois de janvier à décembre.
Ce qui me bloque c'est comment définir l'année effective de ce tableau.. sachant que je suis sur wordpress et qu'avec le plugin que j'utilise je peux pas mettre de formulaire
Sinon je créer un tableau par année.

Merci encore pour ton aide

[jerome1980]

Voici ma requête SQL pour générer un tableau avec le CA mensuel en me basant sur la précédente requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT MONTH(date_encaiss) AS Mois, SUM(montant) AS montant_fact, SUM(CASE WHEN date_encaiss IS NOT NULL THEN montant END) AS montant_encaiss
FROM factures
WHERE YEAR(date_encaiss) = YEAR(CURRENT_DATE)
GROUP BY Mois

Avec des conditions sur ma cellule "mois" qui me remplace les valeurs numériques 10 par "octobre" ect ça marche bien.

Par contre lorsque j'ai aucun montant sur un des mois de l'année (et oui ça arrive...) mon tableau skip le mois en question (c'est logique)
Comme je peux remédier à ça pour avoir les 12 mois d'affiché ?

[al1_24]

Quel que soit le SGBD, il ne peut inventer des informatoins qu'il ne possède pas. Il va donc te falloir une table calendrier avec la liste des mois.
Et effectuer une jointure externe sur cette table pour faire ressortir ces totaux à zéro.

[jerome1980]

Quel que soit le SGBD, il ne peut inventer des informatoins qu'il ne possède pas. Il va donc te falloir une table calendrier avec la liste des mois.
Et effectuer une jointure externe sur cette table pour faire ressortir ces totaux à zéro.

Merci encore al1 pour ta disponibilité, je comprends bien ta remarque oui

Je vais chercher des info sur l'utilisation des jointures externes, ça commence a se corser pour moi ^^

[jerome1980]

Suite au conseil de al1, j'ai mis en place une table (periode) avec les numéros de mois, les champs montant sont tous à 0 :

Mois	montant_fact	montant_encaiss
1	0	0
...	0	0
12	0	0

Pour la jointure externe j'ai essayé la requête suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
SELECT MONTH(COALESCE(date_encaiss, date_fact)) AS Mois , SUM(montant) AS montant_fact , SUM(CASE WHEN date_encaiss IS NOT NULL THEN montant END) AS montant_encaiss
FROM facturation AS f
LEFT JOIN periode AS p ON f.Mois = p.Mois
WHERE YEAR(COALESCE(date_encaiss, date_fact)) = YEAR(CURRENT_DATE)
GROUP BY Mois

Le module WP me retourne une erreur de lecture sur ma requête, je ne sais pas d'où viens le soucis..

[escartefigue]

Bonjour,

Votre critère de jointure est erroné : "mois" n'est pas une colonne de la table FACTURATION
De plus, quel est l'intérêt de la jointure avec la table PERIODE puisque vous n'utilisez rien dans cette table ?
Si vous voulez un résultat pour chaque mois, même si les mois n'ont pas de facture, il faut prendre la PERIODE coté INNER et la table FACTURE en OUTER et non l'inverse

Exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT P.Mois 
     , SUM(F.montant) AS montant_fact 
     , SUM(CASE WHEN F.date_encaiss IS NOT NULL THEN F.montant END) AS montant_encaiss
FROM PERIODE AS P
LEFT JOIN FACTURE AS F
  ON MONTH(COALESCE(date_encaiss, date_fact)) = P.mois
group by P.mois

[jerome1980]

Merci escartefigue pour ton aide

Sur la base de cette correction, le module me donne un message d'erreur

cannot calculate position of Facturation AS F ON MONTH(COALESCE(date_encaiss, date_fact)) = P.mois within group by P.mois

Je pense que je dois être limité dans la mod de la requête par le module Wordpress
C'est pas très grave, je suis arrivé a faire quasiment tout ce que je voulais et c'est grace à votre aide !

Merci encore

[jerome1980]

J'ai corrigé un petit bug dans ma requete toujours en me basant sur l'exemple que m'a indiqué escartefigue

En exécutant cette requête directement dans phpMyAdmin le resultat affiché du tableau n'est pas bon,
les mois sans montant facturé ou encaissé n'apparaissent pas et je comprends pas pourquoi..

le fait qu'un champ avec une date vide correspond à un champ NULL pose problème vous pensez ?

Vous voyez où ça cloche ?

Ma requête :

SELECT P.mois
, SUM(F.montant) AS montant_fact
, SUM(CASE WHEN F.date_encaiss IS NOT NULL THEN F.montant END) AS montant_encaisse
FROM periode AS P
LEFT JOIN facturation AS F
ON MONTH(CASE WHEN F.date_encaiss IS NOT NULL THEN F.date_fact END) = P.mois
WHERE YEAR(COALESCE(F.date_encaiss, F.date_fact)) = 2018
group by P.mois

Pour rappel ma table facturation : (données stocké)

ID	num	projet	montant	date_fact	date_encaiss
Auto	n° facture	nom du projet	montant facturé	date facturation	date encaissement

Ma table periode : (tout les montants sont zero)

mois	montant_fact	montant_encaisse
1	0	0
...	0	0
12	0	0

[Artemus24]

Salut à tous.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `calendrier`
--------------
 
--------------
CREATE TABLE `calendrier`
( `annee`  smallint unsigned  NOT NULL,
  `mois`   tinyint  unsigned  NOT NULL,
  primary key (`annee`,`mois`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `calendrier` (`annee`,`mois`) VALUES
  (2018,1),(2018,2),(2018,3),(2018,4),(2018,5),(2018,6),(2018,7),(2018,8),(2018,9),(2018,10),(2018,11),(2018,12)
--------------
 
--------------
select * from `calendrier`
--------------
 
+-------+------+
| annee | mois |
+-------+------+
|  2018 |    1 |
|  2018 |    2 |
|  2018 |    3 |
|  2018 |    4 |
|  2018 |    5 |
|  2018 |    6 |
|  2018 |    7 |
|  2018 |    8 |
|  2018 |    9 |
|  2018 |   10 |
|  2018 |   11 |
|  2018 |   12 |
+-------+------+
--------------
DROP TABLE IF EXISTS `facture`
--------------
 
--------------
CREATE TABLE `facture`
( `id`            integer unsigned                                                NOT NULL auto_increment primary key,
  `num`              char(08)                                                     NOT NULL,
  `projet`        varchar(255)                                                    NOT NULL,
  `montant`       decimal(15,2)                                                   NOT NULL,
  `annee`         smallint unsigned generated always as  (year(date_fact)) stored NOT NULL,
  `mois`          tinyint  unsigned generated always as (month(date_fact)) stored NOT NULL,
  `date_fact`     date                                                            NOT NULL,
  `date_encaiss`  date                                                                NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `facture` (`num`, `projet`,`montant`,`date_fact`,`date_encaiss`) VALUES
  ('2018-001', 'projet 1', 100.00, '2018-02-05', '2018-03-01'),
  ('2018-002', 'projet 2',  50.00, '2018-04-17', '2018-04-25'),
  ('2018-003', 'projet 3',  75.00, '2018-04-25', '2018-05-03'),
  ('2018-004', 'projet 1',  30.00, '2018-06-02', '2018-06-25'),
  ('2018-005', 'projet 2',  45.00, '2018-06-08', '2018-07-05'),
  ('2018-006', 'projet 3',  12.00, '2018-06-12', '2018-06-25'),
  ('2018-007', 'projet 1',  55.00, '2018-06-25', '2018-07-05'),
  ('2018-008', 'projet 2',  64.00, '2018-09-11', '2018-09-25'),
  ('2018-009', 'projet 3', 200.00, '2018-09-25', '2018-10-03'),
  ('2018-010', 'projet 1', 150.00, '2018-09-11', '2018-09-25'),
  ('2018-011', 'projet 2',  88.00, '2018-10-15', NUll),
  ('2018-012', 'projet 3',  33.00, '2018-10-22', NULL)
--------------
 
--------------
select * from `facture`
--------------
 
+----+----------+----------+---------+-------+------+------------+--------------+
| id | num      | projet   | montant | annee | mois | date_fact  | date_encaiss |
+----+----------+----------+---------+-------+------+------------+--------------+
|  1 | 2018-001 | projet 1 |  100.00 |  2018 |    2 | 2018-02-05 | 2018-03-01   |
|  2 | 2018-002 | projet 2 |   50.00 |  2018 |    4 | 2018-04-17 | 2018-04-25   |
|  3 | 2018-003 | projet 3 |   75.00 |  2018 |    4 | 2018-04-25 | 2018-05-03   |
|  4 | 2018-004 | projet 1 |   30.00 |  2018 |    6 | 2018-06-02 | 2018-06-25   |
|  5 | 2018-005 | projet 2 |   45.00 |  2018 |    6 | 2018-06-08 | 2018-07-05   |
|  6 | 2018-006 | projet 3 |   12.00 |  2018 |    6 | 2018-06-12 | 2018-06-25   |
|  7 | 2018-007 | projet 1 |   55.00 |  2018 |    6 | 2018-06-25 | 2018-07-05   |
|  8 | 2018-008 | projet 2 |   64.00 |  2018 |    9 | 2018-09-11 | 2018-09-25   |
|  9 | 2018-009 | projet 3 |  200.00 |  2018 |    9 | 2018-09-25 | 2018-10-03   |
| 10 | 2018-010 | projet 1 |  150.00 |  2018 |    9 | 2018-09-11 | 2018-09-25   |
| 11 | 2018-011 | projet 2 |   88.00 |  2018 |   10 | 2018-10-15 | NULL         |
| 12 | 2018-012 | projet 3 |   33.00 |  2018 |   10 | 2018-10-22 | NULL         |
+----+----------+----------+---------+-------+------+------------+--------------+
--------------
select    annee,
          sum(coalesce(montant, 0.00))                                                             as facture,
          sum(                          case when date_encaiss is null then 0.00 else montant end) as encaisse,
          sum(coalesce(montant, 0.00) - case when date_encaiss is null then 0.00 else montant end) as diff
 
    from  `facture`
group by  annee
--------------
 
+-------+---------+----------+--------+
| annee | facture | encaisse | diff   |
+-------+---------+----------+--------+
|  2018 |  902.00 |   781.00 | 121.00 |
+-------+---------+----------+--------+
--------------
select           t1.annee,
                 t1.mois,
                 sum(coalesce(montant, 0.00))                                                             as facture,
                 sum(                          case when date_encaiss is null then 0.00 else montant end) as encaisse,
                 sum(coalesce(montant, 0.00) - case when date_encaiss is null then 0.00 else montant end) as diff
 
           from  `calendrier` as t1
 
left outer join  `facture`    as t2
             on  t2.annee = t1.annee
            and  t2.mois  = t1.mois
 
group by  t1.annee, t1.mois
order by  t1.annee, t1.mois
--------------
 
+-------+------+---------+----------+--------+
| annee | mois | facture | encaisse | diff   |
+-------+------+---------+----------+--------+
|  2018 |    1 |    0.00 |     0.00 |   0.00 |
|  2018 |    2 |  100.00 |   100.00 |   0.00 |
|  2018 |    3 |    0.00 |     0.00 |   0.00 |
|  2018 |    4 |  125.00 |   125.00 |   0.00 |
|  2018 |    5 |    0.00 |     0.00 |   0.00 |
|  2018 |    6 |  142.00 |   142.00 |   0.00 |
|  2018 |    7 |    0.00 |     0.00 |   0.00 |
|  2018 |    8 |    0.00 |     0.00 |   0.00 |
|  2018 |    9 |  414.00 |   414.00 |   0.00 |
|  2018 |   10 |  121.00 |     0.00 | 121.00 |
|  2018 |   11 |    0.00 |     0.00 |   0.00 |
|  2018 |   12 |    0.00 |     0.00 |   0.00 |
+-------+------+---------+----------+--------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

@+

[jerome1980]

Merci beaucoup Artemus !

C'est pas un coup de main c'est un vrai tuto

Je vais adapter ma base et mes requêtes grâce à ton code, c'est top merci encore