Requête plus ou moins complexe

**Just-Soft** · 09/07/2014, 17h20

Salut !
voilà j'ai deux tables Achat et règlement, je voudrais créer un journal de mouvement par fournisseur. Les deux tables ont comme jointure code_fournisseur. Donc, je voudrais avoir toutes les opérations ordonnées par date que se soit un achat ou un règlement et comme champ calculé le solde de l'opération. Mais comme les dates d'achat et de règlement ne sont pas en correspondance mon résultat est erroné. Voici le code SQL que j'ai réalisé pour cette requête, mais il semble bien qu'il y'a une erreur sans ma logique.

Code :

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

1
2
3
4
select distinct a.code_fournisseur, a.date_achat,
a.net, r.date_reglement, r.montant_regle,
a.net - r.montant_regle solde
from tb_achat a, tb_reglement_fournisseur rr

En pièce jointe le résultat cette requête.

Merci par avance pour tout aide.

**Ph. B.** · 09/07/2014, 19h24

Bonjour,

La requête que vous avez proposée est un produit cartésien, pas une jointure dont il manque la condition sur le code fournisseur.
La requête suivante est une jointure dont le résultat ne répond que partiellement à votre demande.

Code :

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

1
2
3
4
5
SELECT a.code_fournisseur, a.date_achat,
a.net, r.date_reglement, r.montant_regle
FROM tb_achat a
INNER JOIN tb_reglement_fournisseur r ON r.code_fournisseur = a.code_fournisseur
ORDER BY a.code_fournisseur, r.date_reglement

Ensuite, de manière générale (et cela s'applique à d'autres) pourriez vous donner :

la structure des tables (du moins des colonnes utiles à votre demande),
un jeu d'essai pertinent,
le résultat que vous souhaitez obtenir par rapport à ce jeu d'essai.

**SergioMaster** · 10/07/2014, 09h23

Bonjour,

Je

2 fois Philippe , une fois pour sa proposition SQL et une seconde pour ces recommandations , lorsque l'on demande ce genre de choses , il est bon d'appliquer ces trois points . Le mieux étant de fournir un script de création des tables et insertions des enregistrements , ainsi il est beaucoup plus facile de proposer une solution testée .

je vois au moins une faille à la structure de la table : si je comprends bien un achat peut être réglé en plusieurs fois or il n'y a dans la table règlement aucune référence à l'achat ! AMHA il manque un numéro de pièce (facture/transaction comme il semble bon de l'appeler) sinon le calcul du Solde est totalement inutile

**Just-Soft** · 10/07/2014, 09h51

Philippe - Serge

merci pour les conseils et recommandations. c'est OK pour les propositions mais entre temps j'ai essayé avec une union et ça me donne un premier résultat à discuter. voici mon dernier code :

SELECT a.code_fournisseur, f.nom_fournisseur, a.date_achat,
a.net, null as montant_regle FROM tb_achat a
inner join tb_fournisseur f on (f.code_fournisseur = a.code_fournisseur)

UNION all

SELECT r.code_fournisseur, f.nom_fournisseur, r.date_reglement,null as net,
r.montant_regle FROM tb_reglement_fournisseur r
inner join tb_fournisseur f on (f.code_fournisseur = r.code_fournisseur)

order by 3

Ce qui me manque c'est le solde, donc :
1. comment inclure une colonne solde dans une union ?
2. s'agissant du même fournisseur, le solde doit tenir compte du résultat de la ligne précédente, et là aucune idée sur comment faire ça ?

@Serge : Effectivement, pour le référence sur un achat j'avais pas pensé. Selon la demande de mon client, il cherche le solde par fournisseur mais je pense qu'il est plus juste d'avoir une référence sur l'achat, comme vous l'avez proposer, afin de solder les factures d'achat. merci pour la remarque.

@Philippe : voici la structure des trois tables (les plus importants point de vue requête demandée):
table fournisseur {code_fournisseur, nom_fournisseur}
table achat {code_achat, numero_achat, date_achat, type_piece_achat, numero_piece_achat, code_fournisseur, net}
table reglement {code_reglement, date_reglement, mode_reglement, code_fournissuer, montant_regle}

**SergioMaster** · 10/07/2014, 11h55

Selon la demande de mon client, il cherche le solde par fournisseur

S'il s'agit uniquement des Soldes Fournisseurs alors je proposerais ceci

Code :

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

1
2
3
4
5
SELECT a.code_fournisseur, f.nom_fournisseur, SUM(a.Net) as total_net , SUM(COALESCE(r.montant_regle,0) as total_regle 
FROM tb_achat a 
join tb_fournisseur f on a.code_fournisseur = f.code_fournisseur
left join tb_reglement_fournisseur r on a.Code_fournisseur=r.code_fournisseur
group by 1,2

voire avec une CTE pour calculer le solde

Code :

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

1
2
3
4
5
6
WITH  T AS (SELECT a.code_fournisseur,SUM(a.Net) as total_net, SUM(COALESCE(r.montant_regle,0) as total_regle
FROM tb_achat a 
left join tb_reglement_fournisseur r on a.Code_fournisseur=r.code_fournisseur
group by 1,2 )
 
SELECT T.code_fournisseur,f.nom_fournisseur,T.total_Net,t.total_regle,T.net-T.regle as solde from T join tb_fournisseur f on t.code_fournisseur = f.code_fournisseur

plutôt que

table fournisseur {code_fournisseur, nom_fournisseur}
table achat {code_achat, numero_achat, date_achat, type_piece_achat, numero_piece_achat, code_fournisseur, net}
table reglement {code_reglement, date_reglement, mode_reglement, code_fournissuer, montant_regle}

j'aurais préféré

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
CREATE TABLE FOURNISSEUR
(code_fournisseur INTEGER,
 nom_fournisseur VARCHAR(60),
 CONTRAINT PK_FOURN PRIMARY KEY (code_fournisseur)
) ; 
 
INSERT INTO FOURNISSEUR(code_fournisseur,nom_fournisseur) VALUES (1,'TOTO');
INSERT INTO FOURNISSEUR(code_fournisseur,nom_fournisseur) VALUES (2,'TATOO');
 
etc ....

pour pouvoir faire des tests , ces deux propositions sont faites sans filet

**Ph. B.** · 13/07/2014, 00h30

Bonjour,

Envoyé par Just-Soft

@Serge : Effectivement, pour le référence sur un achat j'avais pas pensé. Selon la demande de mon client, il cherche le solde par fournisseur mais je pense qu'il est plus juste d'avoir une référence sur l'achat, comme vous l'avez proposer, afin de solder les factures d'achat. merci pour la remarque.

@Philippe : voici la structure des trois tables (les plus importants point de vue requête demandée):
table fournisseur {code_fournisseur, nom_fournisseur}
table achat {code_achat, numero_achat, date_achat, type_piece_achat, numero_piece_achat, code_fournisseur, net}
table reglement {code_reglement, date_reglement, mode_reglement, code_fournissuer, montant_regle}

C'est un 1° pas d'avoir ces tables, mais je me permets d'insister, sans jeu d'essai et sans résultat souhaité par rapport à ce jeu d'essai, il est difficile de proposer une solution.
Au préalable, il faut régler ce manque de lien entre règlement et achat.
En effet, tout comptable voudra rattacher un règlement à un achat (de facto, il sera rattaché à un fournisseur car tout achat est fait auprès d'un fournisseur)...

Au strict minimum, il faudrait les tables et les colonnes suivantes :
TB_FOURNISSEUR (CODE_FOURNISSEUR, NOM_FOURNISSEUR)
TB_ACHAT (CODE_ACHAT, DATE_ACHAT, CODE_FOURNISSEUR, NET)
TB_REGLEMENT (CODE_REGLEMENT, DATE_REGLEMENT, CODE_ACHAT, MONTANT_REGLE)

Un jeu d'essai possible serait :
TB_FOURNISSEUR
1, Fournisseur 1
2, Fournisseur 2
TB_ACHAT
1, 01/07/2014, 1, 250
2, 01/07/2014, 2, 500
3, 10/07/2014, 1, 300
4, 10/07/2014, 2, 400
TB_REGLEMENT
1, 05/07/2014, 1, 40
2, 06/07/2014, 2, 100
3, 07/07/2014, 1, 100
4, 07/07/2014, 2, 200
5, 08/07/2014, 1, 110
6, 09/07/2014, 2, 200
7, 11/07/2014, 3, 100

Comme résultat par achat: (CODE_FOURNISSEUR, CODE_ACHAT, NET, TOTAL_REGLE, SOLDE)
1, 1, 250, 250, 0
1, 3, 300, 100, 200
2, 2, 500, 500, 0
2, 4, 400, 0, 400

Comme résultat par fournisseur: (CODE_FOURNISSEUR, TOTAL_NET, TOTAL_REGLE, SOLDE)
1, 550, 350, 200
2, 900, 500, 400

A partir de ces données, on peut proposer une réponse au minimum consistante, au mieux vraie...

Serge, la 1° requête est AMHA erronée : la somme sur le net se fait sur le résultat d'une jointure (pour 2 règlements d'un achat, on va cumuler 2 fois le même net).
La clause WITH de la CTE est syntaxiquement incorrecte, une colonne avec une fonction d'agrégation ne peut pas faire partie de la clause de regroupement.

**SergioMaster** · 13/07/2014, 09h03

@Just-Soft regles-forum-langage sql à lire/
cela s'applique aux rubriques SQL de chaque SGBD

@Philippe

Envoyé par SergioMaster

ces deux propositions sont faites sans filet

pour la CTE , effectivement j'ai groupé un peu trop

, j'avais d'abord fait un copier collé de la première (avec le nom fournisseur) que j'ai ôté ensuite pour le mettre dans la requête 'principale' mais j'ai effectivement oublié d'enlever le critère de groupe

quant à la première proposition , effectivement elle est fausse , faute à un jeu d'essai. Tombé du lit et n'ayant rien de mieux

à faire sans bruit , je me suis fait un petit jeu d'essai moche (pas de PK , pas de CONTRAINTES !!!, manque des cas )

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
 
CREATE TABLE FOURNISSEUR
( CODE INTEGER,
  NOM VARCHAR(30)
);
COMMIT;
 
INSERT INTO FOURNISSEUR VALUES (1,'TOTO');
INSERT INTO FOURNISSEUR VALUES(2,'TATA');
INSERT INTO FOURNISSEUR VALUES(3,'TUTU');
 
CREATE TABLE ACHAT
( FOURNISSEUR INTEGER,
  MONTANT_NET NUMERIC(15,2)
);
COMMIT;
INSERT INTO ACHAT VALUES(1,100);
INSERT INTO ACHAT VALUES(2,200);
 
CREATE TABLE REGLEMENT
( FOURNISSEUR INTEGER,
  MONTANT_VERSE NUMERIC(15,2)
);
COMMIT;
INSERT INTO REGLEMENT VALUES(1,10);
INSERT INTO REGLEMENT VALUES(1,10);
INSERT INTO REGLEMENT VALUES(1,10);
INSERT INTO REGLEMENT VALUES(1,10);
INSERT INTO REGLEMENT VALUES(1,10);
INSERT INTO REGLEMENT VALUES(1,10);
INSERT INTO REGLEMENT VALUES(2,150);

une première proposition (pas terrible)

Code :

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

1
2
3
4
5
6
7
SELECT F.CODE,F.NOM, 
(SELECT COALESCE(SUM(MONTANT_NET),0) FROM ACHAT WHERE FOURNISSEUR=f.CODE) AS total_net ,
(SELECT COALESCE(SUM(montant_verse),0) FROM REGLEMENT WHERE FOURNISSEUR=f.CODE) AS total_regle,
(SELECT COALESCE(SUM(MONTANT_NET),0) FROM ACHAT WHERE FOURNISSEUR=f.CODE)-
(SELECT COALESCE(SUM(montant_verse),0) FROM REGLEMENT WHERE FOURNISSEUR=f.CODE) AS SOLDE
 
FROM FOURNISSEUR F

une plus jolie , grâce aux CTEs

Code :

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

1
2
3
4
5
6
7
WITH A AS (SELECT FOURNISSEUR,SUM(MONTANT_NET) AS TOTAL_NET FROM ACHAT GROUP BY 1),
     R AS (SELECT FOURNISSEUR,SUM(MONTANT_VERSE) AS TOTAL_REGLE FROM REGLEMENT GROUP BY 1) 
 
SELECT F.CODE,F.NOM,COALESCE(A.TOTAL_NET,0) AS NET,COALESCE(R.TOTAL_REGLE,0) AS REGLE,COALESCE(A.TOTAL_NET,0)-COALESCE(R.TOTAL_REGLE,0) AS SOLDE 
FROM FOURNISSEUR F
LEFT JOIN A ON F.CODE=A.FOURNISSEUR
LEFT JOIN R ON F.CODE=R.FOURNISSEUR

résultats conformes au jeu d'essai

1 TOTO 100.00 60.00 40.00
2 TATA 200.00 150.00 50.00
3 TUTU 0.00 0.00 0.00

Requête plus ou moins complexe

SQL Firebird

Discussions similaires

Partager

Partager