Comparaison dans une même table des montants avec %

**vpavince** · 12/01/2015, 11h22

Bonjour à tous,[

Je commence avec SQL et je dois extraire de la base des "faux doublons" :
Je dois extraire des factures pour une même personne, pour un même numéro de facture avec un montant différent de 5%.

Merci pour votre aide car je n'ai pas trouvé le moyen. Les factures sont dans la même table. J'ai commencé avec ceci pour extraire les doublons mais là je suis bloqué car se sont des " faux doublons" :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT DISTINCT *
FROM detect.facture_ps AS t1
WHERE EXISTS
    (SELECT *
     FROM detect.facture_ps AS t2
	WHERE t1.fect_idtech <> t2.fect_idtech 
	AND t1.idfactpk = t2.idfactpk
	AND t1.fk_idadh = t2.fk_idadh 
	AND t1.datefact = t2.datefact 
	AND t1.mttotalremlbfact = t2.mttotalremlbfact
    AND t1.mttotalremlbfact > 0
    )
ORDER BY t1.idfactpk

**escartefigue** · 12/01/2015, 12h54

Bonjour,
Je ne comprends pas bien, dans l'énoncé tu parles de factures avec des montants différents, or tu construit ta jointure avec égalité sur le n° de facture et le montant.

Comment peut on avoir 2 fois le même n° de facture dans une table facture (il faudrait joindre le MPD avec les index), et si vraiment présent 2 fois, n'y a -t- il pas un code genre annulation qui ferait que seule une des 2 est valide. Sinon je ne comprends pas les montants diiférents.

Par ailleurs quelques remarques :

- un distinct sur un select * ça va couter cher en tri ! est-ce vraiment utile, j'en doute
- select * idem, as tu vraiment besoin de toutes les colonnes, transporter des données a aussi un cout, et on a rarement besoin de tout
- en cas d'écart de montant, ne faut il pas comparer les éléments composants la facture (articles facturés, quantités, prestations annexes) pour expliquer les doublons (en conservant l'idée que seule une des 2 factures est valide, quelques explications sur ce point particulier sont nécessaires) ?

**StringBuilder** · 12/01/2015, 13h35

remplacer and t1.mttotalremlbfact = t2.mttotalremlbfact par :

Code sql :

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

1
2
 
and (abs(t1.mttotalremlbfact - t2.mttotalremlbfact) / t1.mttotalremlbfact < .05 or abs(t1.mttotalremlbfact - t2.mttotalremlbfact) / t2.mttotalremlbfact < .05)

**vpavince** · 12/01/2015, 13h57

Bonjour StringBuilder,

La comparaison est bien mais j'ai un problème avec des factures qui sont égales à 0.
Il ne s'agit pas de facture comptable mais des factures provenant de professionnel de santé qui peuvent emmètre des montants à 0.

Code sql :

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

1
2
Erreur SQL :
ERREUR:  division par zéro

Comment les exclure avant le calcul ?

Merci

**StringBuilder** · 12/01/2015, 14h11

Rajouter un test sur la valeur zéro pour écarter du test les factures où t1 = 0 ou t2 = 0, et exclure les factures où t1 = 0 et t2 = 0.

Je te laisse écrire seul cette partie, c'est trivial à écrire.

**escartefigue** · 12/01/2015, 14h45

Envoyé par StringBuilder

Rajouter un test sur la valeur zéro pour écarter du test les factures où t1 = 0 ou t2 = 0, et exclure les factures où t1 = 0 et t2 = 0.

Je te laisse écrire seul cette partie, c'est trivial à écrire.

Désolé d'insister, mais pour ma part, les solutions techniques n'auront de sens que lorsque j'aurai compris le contexte, or, je ne comprends toujours pas comment on peut avoir 2 factures avec le même numéro d'une part, et qui plus est avec des montants différents d'autre part.

Soit l'une des 2 est annulée auquel cas il n'y a plus de doublon, soit... bah j'en sais rien

**StringBuilder** · 12/01/2015, 15h20

Dans sa table, il y a :
- fect_idtech : visiblement un identifiant "technique", certainement autoincrément, unique.
- idfactpk : numéro de facture "humain" (par exemple, préfixé avec l'année et le jour, compteur par client, etc.), et contraitement au suffixe "pk", qui n'est pas une clé primaire (non unique)

Pour des raisons X ou Y, plusieurs factures ont le même numéro "humain". Il faut voir ce numéro comme un commentaire, un simple texte. Le véritable identifiant est bien fect_idtech.

Certains "doublons" (idfactpk identiques) semblent voulus (par exemple, la facture et ses avoirs auront par exemple le même numéro), mais d'autre ne le sont pas : saisie en doublon, erreur d'interface, etc.

Il cherche donc à identifier les "doublons" pour lesquels le montant total est similaire (moins de 5% d'écart) afin de se pencher dessus pour déterminer s'il s'agit d'erreurs, ou de véritables factures différentes.

Imagine un artisan qui utilise des talons numérotés pour ses factures, et que ces talons aillent de 1 à 1000.
Si par hasard dans la même année, il fait plus de 1000 factures, son compteur de talons va boucler, et quand il va ressaisir ses factures dans son outil informatique, que sa femme lui a offert à Noël (ou que son comptable lui a obligé d'acheter), il va être dans le cas de notre ami.
=> Il faut donc, en présence de ces doublons, vérifier si ça correspond bien à des factures réelles ou des erreurs.

**vpavince** · 12/01/2015, 15h27

C'est bien cela StringBuilder.

En revanche peux-tu me dire comment extraire les factures à 0 car je suis un débutant et je ne vois pas comment je peux faire.

Merci d'avance.

**StringBuilder** · 12/01/2015, 15h31

Tu fais pas trop d'effort...

Code sql :

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

1
2
3
4
5
 
and (
   (t1.mttotalremlbfact = 0 and mttotalremlbfact = 0)
   or
   (t1.mttotalremlbfact <> 0 and mttotalremlbfact <> 0 and (abs(t1.mttotalremlbfact - t2.mttotalremlbfact) / t1.mttotalremlbfact < .05 or abs(t1.mttotalremlbfact - t2.mttotalremlbfact) / t2.mttotalremlbfact < .05))

**islamov2000** · 14/01/2015, 09h30

Si la table est bien conçue, on ne peut pas trouver plus d'une facture avec même N° facture et même personne.

**SQLpro** · 14/01/2015, 10h18

Pour les factures en différences à 5% au maximum, faites :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT DISTINCT *
FROM detect.facture_ps AS t1
WHERE EXISTS
    (SELECT *
     FROM detect.facture_ps AS t2
	WHERE t1.fect_idtech <> t2.fect_idtech 
	AND t1.idfactpk = t2.idfactpk
	AND t1.fk_idadh = t2.fk_idadh 
	AND t1.datefact = t2.datefact 
	AND ABS(t1.mttotalremlbfact - t2.mttotalremlbfact) < 0.05 * ABS(t1.mttotalremlbfact - t2.mttotalremlbfact) / 2
    AND t1.mttotalremlbfact <> 0
    )
ORDER BY t1.idfactpk

A +