Discussion :

[Louis-Guillaume Morand]

Bonjour à tous,

J'aurais besoin de l'aide d'experts pour me tripatouiller de cette requête sur laquelle je suis depuis quelques heures (et on se moque pas )

Objectif:
---------

Pour un client donné, afficher la somme des reglements recus selon l'état courrant du dossier

J'ai deux tables: (je degage tout ce qui sert à rien)
T_Dossiers, T_Reglements

T_Dossiers
-------------
Num_Client -> le numéro du client auquel appartient le dossier
Num_Dos -> la clé primaire du dossier
Montant -> le montant du dossier
Etat -> l'etat du dossier (pause, terminé, fini, etc)

T_Reglements
--------------
Reg_id => clé primaire des réglements
Num_Deb => num client qui possede le dossier qui possede ce reglement
Num_Dos => Dossier auquel se rapport le reglement
Montant => montant du reglement
Date => date du reglement

On peut déjà constater un champ Num_Deb totalement inutile et je peux vous dire que la BDD en possède des dizaines comme ca. elle est mal foutue dans tous les sens avec des duplications de champs par milliers MAIS je n'ai pas le droit de modifier la base. Les champs ont les même noms mais les relations n'ont même pas été codées dans la base donc les contraintes, c'est de l'optionnel pour le developpeur de cette boite



Bref, je pensais avoir la solution avec une requête de la forme:

Code :

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

1
2
3
4
5
6
7
8
9
 
SELECT T_Dossier.Etat, 
Count(T_Dossier.Num_Deb) AS [Nbre de dossiers], 
Format(Sum(CCur(IIf(IsNull(T_Reglement.Montant),0,T_Reglement.Montant))),'# 0.00') AS Encaisse, 
Format(Sum(CCur(T_Dossier.Montant)),'# 0.00') AS [Montant confié]
 
FROM T_Dossier LEFT JOIN T_Reglement ON T_Dossier.Num_Dos = T_Reglement.Num_Dos
WHERE (((T_Dossier.Num_Client)=6024))
GROUP BY T_Dossier.Etat;

malheureusement comme un dossier peut avoir plusieurs règlements, ca multiplie le nombre d'enregistrements et donc les calculs et je me retrouve avec un nombre de dossier trop grand puis des calculs de somme faux

Donc j'ai voulu faire par étapes:

Code :

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

1
2
3
SELECT count(Dossier.Num_Dos)
FROM Dossier
WHERE Num_Client=6024;

==> 1618 dossiers

Code :

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

1
2
3
SELECT COUNT(Dossier.Num_Dos) 
FROM Dossier LEFT JOIN Reglement ON Dossier.Num_Dos = Reglement.Num_Dos
WHERE Num_Client=6024

==> 1628 dossiers
10 de plus. pourquoi?

Il y a 10 dossiers ayant 2 reglements
et 3 dossiers ayant 3 reglements.
donc j'aurais eu +13dossiers ou +16, j'aurais pu éventuellement trouver une explication mais là...

J'ai pensé à un regroupement mais lequel? que je teste par dossier, debiteur ou même par client, dès que je fais la jointure entre Dossier et Reglement, j'ai une erreur de compte. Comme beaucoup de dossiers n'ont pas de reglement, la LEFT JOIN est obligatoire.

Si quelqu'un avait le temps de me donner un coup de main
je lui en serait très reconnaissant

[fsmrel]

Un problème d'intégrité référentielle non respectée ?

Il faudrait déjà s'en assurer. Dans le même temps, on peut regarder ce que donnent les fonctions d'agrégation :

Code :

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

1
2
3
4
5
6
7
8
 
Select Num_Dos, Num_Deb as Num_CLient, 
         Sum(Montant) As Total_Rgl, Count(Num_Dos) As Nb_Rgl_Par_Dos, 
         'Viol intégrité' as Etat
From   Reglement r
         Where not Exists 
         (Select * From Dossier d Where r.Num_Dos = d.Num_Dos)
Group By  Num_Deb, Num_Dos ;

On peut aussi calculer pour la partie qui respecte l'intégrité référentielle :

Code :

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

1
2
3
4
5
6
7
8
9
 
Select d.Num_Dos, d.Num_Client, r.Total_Rgl, r.Nb_Rgl_Par_Dos, d.Etat
From   Dossier As d, 
      (Select Num_Dos, 
       Sum(Montant) as Total_Rgl, Count(Num_Dos) As Nb_Rgl_Par_Dos 
       From Reglement 
       Group By Num_Dos) As r
Where  d.Num_Dos = r. Num_Dos
Group By d.Num_Client, d.Etat, d.Num_Dos, r.Total_Rgl, r.Nb_Rgl_Par_Dos ;

Puis y aller avec la totale par union des deux requêtes précédentes :

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
 
Select Num_Dos, Num_Deb as Num_CLient, 
         Sum(Montant) As Total_Rgl, Count(Num_Dos) As Nb_Rgl_Par_Dos, 
         'Viol intégrité' as Etat
From   Reglement r
         Where not Exists 
         (Select * From Dossier d Where r.Num_Dos = d.Num_Dos)
Group By  Num_Deb, Num_Dos 
Union 
Select d.Num_Dos, d.Num_Client, r.Total_Rgl, r.Nb_Rgl_Par_Dos, d.Etat
From   Dossier As d, 
      (Select Num_Dos, 
       Sum(Montant) as Total_Rgl, Count(Num_Dos) As Nb_Rgl_Par_Dos 
       From Reglement 
       Group By Num_Dos) As r
Where  d.Num_Dos = r. Num_Dos
Group By d.Num_Client, d.Etat, d.Num_Dos, r.Total_Rgl, r.Nb_Rgl_Par_Dos ;

Peut-être y verra-t-on plus clair ? Quoi qu'il en soit, on vérifie ainsi ce qui se passe du côté des dossiers orphelins, reste maintenant à s'assurer de ce qu'il en est des numéros de client...

Pour blinder l'intégrité, il est évident qu'il faudrait définir une clé étrangère, entre le couple {Num_Client, Num_Dos} de Dossier et le couple {Num_Deb, Num_Dos} de Reglement. C'est juste un petit Alter Table (clause Unique dans Dossier, Foreign Key dans Reglement...)

Evidemment, si le SGBD détecte des orphelins en Prod, il va coincer : commencer par faire cela dans un environnement à part, y affecter de parents aux orphelins et quand tout est bon, répercuter en Prod.

Maintenant, si vos interlocuteurs refusent alors que l'on ne touche pas à la structure de la table, tant pis pour eux si la base de données en a un coup.

[Mindiell]

Voilà une requête unique qui devrait fonctionner normalement :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT Etat, COUNT (Etat) AS Nombre_Dossiers, SUM ( Montant ) AS Montant
FROM (
SELECT Num_Dos, Etat, (
	SELECT SUM (Montant)
	FROM T_Reglements
	WHERE T_Reglements.Num_Dos = T_Dossiers.Num_Dos
	AND T_Reglements.Num_Client = T_Dossiers.Num_Deb
	) AS Montant
FROM T_Dossiers
WHERE T_Dossiers.Num_Client = 'XXXXX'
)
GROUP BY Etat

Pour expliquer, je prends tous les dossiers d'un client, et pour chaque dossier, je fais la somme des reglements. Ensuite, à partir de cette requête, je fais mon "group by" par etat des dossiers...

[Louis-Guillaume Morand]

merci de ta réponse
Alors, pour la première requête

Code :

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

1
2
3
4
5
6
7
SELECT Num_Dos, Num_Deb AS Num_CLient, 
         Sum(Montant) AS Total_Rgl, Count(Num_Dos) AS Nb_Rgl_Par_Dos, 
         'Viol intégrité' AS Etat
FROM   Reglement r
         WHERE NOT EXISTS 
         (SELECT * FROM Dossier d WHERE r.Num_Dos = d.Num_Dos)
GROUP BY  Num_Deb, Num_Dos ;

ca ne me ressort RIEN. Je suppose que c'est bon


la seconde me ressort tous plein de resultats (520) avec une colonne de Nb_Rgl_Par_Dos allant de 1 à 18 et dont la somme totale est (comptée deux fois) 1504 alors qu'il y a 1618 reglements en tout.


l'execution des deux requêtes réunies, la premiere etant nulle, me sort donc le meme affichage que la seconde.

Quant à mes interlocuteur, le problème n'est seulement la base en elle même qui a été faite par un "expert oracle" (c'est quand meme la deuxieme fois où je suis dans une boite où je bosse sur une BDD access créee par lui ) et surtout différents outils internes à la boite bossent sur cette base et certaines modifs de tables qui sont pour moi necessaires (tables de jointure et autres) necessiteraient de tout refaire ce qui est hors de question pour le client....

[Louis-Guillaume Morand]

merci aussi Mindiell pour ta réponse mais ca n'est pas tout a fait cela.
Autant les colonnes et l'affichage général ressemble, autant si j'aditionne les dossiers, j'obtiens 1616 dossiers pour le client XXXX alors qu'il en possède 1618.
cela pourrait se traduire par deux dossiers sans reglements et j'ai cru que rajouter une LEFT JOIN solutionnerait la chose mais non j'obtiens tjs 1616.

l'autre probleme c'est que la somme des montant dans la 3eme colonne a un GROS total d'envrions 300 millions d'euros quand la somme des reglements pour ce client atteint 18 000€. bon ok, le client sera content avec un chiffre d'affaires qui dépasserait le milliard mais j'ai peur que ca soit un peu gros pour lui ^^

je vais continuer à faire les tests "parallèles" à la "fsmrel" pour voir s'il s'y trouve une erreur (notez qu'un autre progiciel de la boite affiche ce que je veux mais la requete se trouve dans Access meme en vba et j'ai pas le droit de la voir \o/)
la structure de la requête de Mindiell va m'inspirer aussi. à moi de chercher un peu maintenant. je reviendrai aux nouvelles.