Requête complexe avec jointures, rollup et calculs

Question

Voici mon problème:

Je dois faire une requête pour calculer les commissions sur ventes de commerciaux
J'ai cette requête qui me donne bien les montants à commissionner, cumulés pour chaque agence, puis par commercial puis par type de contact :


SELECT  a.ville_agence, e.nom_employe, t.titre_type_contact, d.id_agence, d.id_employe, d.id_type_contact, d.id_dossier, SUM(d.montant_ht)
FROM dossiers d
JOIN agences a ON d.id_agence=a.id_agence
JOIN employes e ON d.id_employe=e.id_employe
JOIN type_contact t ON d.id_type_contact=t.id_type_contact
WHERE d.id_statut_com=0
GROUP BY d.id_agence, d.id_employe, d.id_type_contact, d.id_dossier
WITH ROLLUP
Voici ce que cela donne.....
( en fonction du nombre de NULL sur la ligne je sais si c'est un sous-total de id_type_contact ou de id_employe ou de id_agence ou total general )


ville_agence  nom_employe  titre_type_contact	id_agence  id_employe	id_type_contact	id_dossier	SUM(d.montant_ht)	com ???
ANGERS		DUPONT		Passif RVF	49		13	10		10900062	5990,5
ANGERS		DUPONT		Passif RVF	49		13	10		NULL		5990,5
ANGERS		DUPONT		Actif RVA	49		13	20		10900067	6066,3
ANGERS		DUPONT		Actif RVA	49		13	20		10900068	9573,5
ANGERS		DUPONT		Actif RVA	49		13	20		10900071	5990,5
ANGERS		DUPONT		Actif RVA	49		13	20		NULL		21630,3
ANGERS		DUPONT		Actif RVA	49		13	NULL		NULL		27620,8		-> CA commercial
ANGERS		POIRIER		Actif RVA	49		21	20		10900070	14691,94
ANGERS		POIRIER		Actif RVA	49		21	20		NULL		14691,94
ANGERS		POIRIER		Actif RVA	49		21	NULL		NULL		14691,94
ANGERS		POIRIER		Actif RVA	49		NULL	NULL		NULL		42312,74
LILLE		DURAND		Actif RVA	59		3	20		10900075	11374,41
LILLE		DURAND		Actif RVA	59		3	20		NULL		11374,41
LILLE		DURAND		Actif RVA	59		3	NULL		NULL		11374,41
LILLE		MARTIN		Passif RVF	59		9	10		10900048	16113,74
LILLE		MARTIN		Passif RVF	59		9	10		10900049	18957,34
LILLE		MARTIN		Passif RVF	59		9	10		NULL		35071,08
LILLE		MARTIN		Actif RVA	59		9	20		10900069	18957,34
LILLE		MARTIN		Actif RVA	59		9	20		NULL		18957,34
LILLE		MARTIN		Actif RVA	59		9	NULL		NULL		54028,42
LILLE		HUGO		Passif RVF	59		10	10		10900064	7488,15
LILLE		HUGO		Passif RVF	59		10	10		NULL		7488,15
LILLE		HUGO		Passif RVF	59		10	NULL		NULL		7488,15
LILLE		RABELAIS	Actif RVA	59		20	20		10900074	19905,21
LILLE		RABELAIS	Actif RVA	59		20	20		NULL		19905,21
LILLE		RABELAIS	Actif RVA	59		20	NULL		NULL		19905,21
LILLE		RABELAIS	Actif RVA	59		NULL	NULL		NULL		92796,19
COLMAR		MOLIERE		Actif RVA	68		5	20		10900052	16113,74
COLMAR		MOLIERE		Actif RVA	68		5	20		NULL		16113,74
COLMAR		MOLIERE		Actif RVA	68		5	NULL		NULL		16113,74
COLMAR		LAMARTINE	Passif RVF	68		11	10		10900050	6161,13
COLMAR		LAMARTINE	Passif RVF	68		11	10		NULL		6161,13
COLMAR		LAMARTINE	Passif RVF	68		11	NULL		NULL		6161,13
COLMAR		BAUDELAIRE	Actif RVA	68		24	20		10900072	23601,89
COLMAR		BAUDELAIRE	Actif RVA	68		24	20		10900073	16722,41
COLMAR		BAUDELAIRE	Actif RVA	68		24	20		NULL		40324,3
COLMAR		BAUDELAIRE	Actif RVA	68		24	NULL		NULL		40324,3
COLMAR		BAUDELAIRE	Actif RVA	68		NULL	NULL		NULL		62599,17
COLMAR		BAUDELAIRE	Actif RVA	NULL		NULL	NULL		NULL		197708,1




 

La commission sur chaque dossier est calculée pour chaque commercial :

-> son chiffre total détermine un taux de commission global taux_com
-> le taux de commission global est fonction également du type de contact d.id_type_contact et de son poste e.id_fonction



Exemple table table_taux_com pour id_fonction=30

id_fonction		id_type_contact		seuil_min_ca		seuil_max_ca		taux_com

30			10			0			19999			0.00
30			10			20000			49999			0.02
30			10			50000			9999999999999		0.05	
30			20			0			19999			0.00
30			20			20000			49999			0.04
30			20			50000			9999999999999		0.07
30			30			0			19999			0.00
30			30			20000			49999			0.06
30			30			50000			9999999999999		0.08


Le calcul est quelquechose comme :

com = SUM(d.montant_ht) c-à-dire la colonne précédente * taux_com sélectionné dans la table ci-dessus en fonction du CA du commercial

si un commercial fait 25000 de CA total, il aura 2% sur ce qu'il a réalisé pour id_type_contact=10, 4% sue le chiffre pour id_type_contact=20,
plus 6% sur id_type_contact=30

Sui-je assez clair ? Ca fait 3 jours que je suis sur cette seule requête et je ne m'en sort pas. Pouvez-vous m'aider ?

kfran85 · Answer

les autres tables sont :

table_type_contact
id_type_contact    titre_type_contact
      10             Passif RVF 
      20             Actif RVA 
      30             GMS - Salons - Foires
 


Agences
id_agence
nom_agence
adresse_agence
cp_agence
ville_agence
...

fonctions
id_fonction
titre_fonction

employes
id_employe
nom_employe
prenom_employe
id_agence
id_fonction

dossiers
id_dossier
date_dossier
id_client
id_type_contact
id_agence
id_employe
montant_ht

kfran85 · Answer

C'est bon j'ai trouvé.

Requête complexe avec jointures, rollup et calculs

2 réponses

Votre réponse

Discussions similaires