PHPfrance

la communauté d'entraide francophone dédiée au PHP

Requête complexe avec sous-requêtes

Répondre


Cette question est un moyen d’empêcher des soumissions automatisées de formulaires par des robots.
Smileys
:D :) :( :o :shock: :? 8-) :lol: :x :P :oops: :cry: :evil: :twisted: :roll: :wink: :!: :?: :idea: :arrow: :| :mrgreen: =D> #-o =P~ :^o :non: :priere: 8-|
Voir plus de smileys
  Revue du sujet
 

  Étendre la vue Revue du sujet : Requête complexe avec sous-requêtes

Re: Requête complexe avec sous-requêtes

par franck85 » 03 sept. 2009, 18:21

C'est bon j'ai trouvé.
Merci

Re: Requête complexe avec sous-requêtes

par franck85 » 03 sept. 2009, 09:41

En fait j'ai du mal expliquer la règle de calcul des commissions:

- on prend en référence le CA total du commercial pour définir la tranche min / max qui sera prise en compte pour déterminer le taux à appliquer
ex: si CA total du commercial = 5000 en RVF + 6000 en RVA + 19000 en GMS ( les 3 types de contact ), on est dans la tranche 20000 à 49999.99 donc on applique respectivement 4%, 6% et 3% à chacun des dossiers de vente

Il faudrait donc une étape intermédiaire ou on calcul le CA total du commercial
CA                             CA total    taux       CA         com

49  13 30 10 10900062 5990.50  27621.00    0.04  *  5990.50   =  239.62

49  13 30 20 10900067 6066.30  27621.00    0.06  *  6066.30   =  363.98
49  13 30 20 10900068 9573.50  27621.00    0.06  *  9573.50   =  574.41
49  13 30 20 10900071 5990.50  27621.00    0.06  *  5990.50   =  359.43
 
                               ^  ^  ^
            TOTAL    27621.00  -> pour id_type_contact = 10 taux_com 0.04
                               -> pour id_type_contact = 20 taux_com 0.06
- Il n'y a plus les "NULL" qui déterminaient le CA total catégorie / total commercial / total agence et total général

- Idéalement, pour le total agence il y a aussi un calcul spécifique de commission
( table taux_com où id_type_contact=0 )

Voici la table reelle et complète taux_com
--
-- Structure de la table `taux_com`
--

CREATE TABLE IF NOT EXISTS `taux_com` (
  `id_taux_com` smallint(4) unsigned NOT NULL,
  `id_fonction` tinyint(3) unsigned NOT NULL,
  `id_type_contact` tinyint(3) unsigned NOT NULL,
  `seuil_min_com` decimal(15,2) unsigned NOT NULL,
  `seuil_max_com` decimal(15,2) unsigned NOT NULL,
  `taux_com` decimal(3,3) unsigned NOT NULL,
  PRIMARY KEY  (`id_taux_com`),
  KEY `id_fonction` (`id_fonction`),
  KEY `id_type_contact` (`id_type_contact`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

--
-- Contenu de la table `taux_com`
--

INSERT INTO `taux_com` (`id_taux_com`, `id_fonction`, `id_type_contact`, `seuil_min_com`, `seuil_max_com`, `taux_com`) VALUES
(340, 20, 10, 70000.00, 99999.99, 0.070),
(330, 20, 10, 50000.00, 69999.99, 0.060),
(320, 20, 10, 20000.00, 49999.99, 0.050),
(310, 20, 10, 10000.00, 19999.99, 0.020),
(300, 20, 10, 0.00, 9999.99, 0.000),
(240, 30, 30, 100000.00, 9999999999999.99, 0.060),
(230, 30, 30, 70000.00, 99999.99, 0.050),
(220, 30, 30, 50000.00, 69999.99, 0.040),
(210, 30, 30, 20000.00, 49999.99, 0.030),
(200, 30, 30, 0.00, 19999.99, 0.000),
(150, 30, 20, 100000.00, 9999999999999.99, 0.090),
(140, 30, 20, 70000.00, 99999.99, 0.080),
(130, 30, 20, 50000.00, 69999.99, 0.070),
(120, 30, 20, 20000.00, 49999.99, 0.060),
(110, 30, 20, 10000.00, 19999.99, 0.030),
(100, 30, 20, 0.00, 9999.99, 0.000),
(60, 30, 10, 100000.00, 9999999999999.99, 0.070),
(50, 30, 10, 70000.00, 99999.99, 0.060),
(40, 30, 10, 50000.00, 69999.99, 0.050),
(30, 30, 10, 20000.00, 49999.99, 0.040),
(20, 30, 10, 10000.00, 19999.99, 0.020),
(10, 30, 10, 0.00, 9999.99, 0.000),
(350, 20, 10, 100000.00, 9999999999999.99, 0.080),
(400, 20, 20, 0.00, 9999.99, 0.000),
(410, 20, 20, 10000.00, 19999.99, 0.020),
(420, 20, 20, 20000.00, 49999.99, 0.050),
(430, 20, 20, 50000.00, 69999.99, 0.060),
(440, 20, 20, 70000.00, 99999.99, 0.070),
(450, 20, 20, 100000.00, 9999999999999.99, 0.080),
(500, 20, 30, 0.00, 9999.99, 0.000),
(510, 20, 30, 10000.00, 19999.99, 0.020),
(520, 20, 30, 20000.00, 49999.99, 0.050),
(530, 20, 30, 50000.00, 69999.99, 0.060),
(540, 20, 30, 70000.00, 99999.99, 0.070),
(550, 20, 30, 100000.00, 9999999999999.99, 0.080),
(600, 20, 0, 0.00, 49999.99, 0.000),
(610, 20, 0, 50000.00, 99999.99, 0.010),
(620, 20, 0, 100000.00, 199999.99, 0.015),
(630, 20, 0, 200000.00, 9999999999999.99, 0.020);
Pas toujours évident à expliquer tout ça, merci pour votre aide

Re: Requête complexe avec sous-requêtes

par sadeq » 03 sept. 2009, 00:59

le problème doit être résolu en 2 étapes:
1. calculer d'abord les CA des commerciaux
2. déterminer pour chaque commercial son taux de com suivant les règles de la table "taux_com"

En effet, si on connait les infos : id_fonction, id_type_contact et le CA pour un employé (un commercial) on peut facilement déterminer son taux_com. Et c'est effectivement ces infos que ta première requête renvoi. Donc on peut tout résoudre en 2 requêtes imbriquées.

Voici comment: 
SELECT r1.id_employe, r1.ca_com, tc.taux_com
FROM taux_com tc,
     (SELECT a.ville_agence, d.id_agence, d.id_employe, 
        e.nom_employe, e.id_fonction, d.id_type_contact, 
        t.titre_type_contact, SUM( d.montant_ht ) AS ca_com
     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 ) as r1

WHERE tc.id_fonction = r1.id_fonction 
and tc.id_type_contact = r1.id_type_contact
and r1.ca_com between tc.seuil_min_ca and tc.seuil_max_ca
Et voici le script de la base de données que j'ai utilisée pour tester : 
-- phpMyAdmin SQL Dump
-- version 3.2.0.1
-- http://www.phpmyadmin.net
--
-- Serveur: localhost
-- Généré le : Mer 02 Septembre 2009 à 22:51
-- Version du serveur: 5.1.31
-- Version de PHP: 5.3.0

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Base de données: `test`
--

-- --------------------------------------------------------

--
-- Structure de la table `agences`
--

CREATE TABLE IF NOT EXISTS `agences` (
  `id_agence` int(11) NOT NULL DEFAULT '0',
  `nom_agence` varchar(255) DEFAULT NULL,
  `adresse_agence` varchar(255) DEFAULT NULL,
  `cp_agence` int(11) DEFAULT NULL,
  `ville_agence` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id_agence`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Contenu de la table `agences`
--

INSERT INTO `agences` (`id_agence`, `nom_agence`, `adresse_agence`, `cp_agence`, `ville_agence`) VALUES
(49, 'Agence 49', 'Agence d''Angers', 49000, 'Angers'),
(59, 'Agence 59', 'Agence de Lille', 59000, 'Lille');

-- --------------------------------------------------------

--
-- Structure de la table `dossiers`
--

CREATE TABLE IF NOT EXISTS `dossiers` (
  `id_dossier` int(11) NOT NULL DEFAULT '0',
  `date_dossier` datetime DEFAULT NULL,
  `id_client` int(11) DEFAULT NULL,
  `id_type_contact` int(11) DEFAULT NULL,
  `id_agence` int(11) DEFAULT NULL,
  `id_employe` int(11) DEFAULT NULL,
  `montant_ht` float DEFAULT NULL,
  `id_statut_com` int(11) NOT NULL,
  PRIMARY KEY (`id_dossier`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Contenu de la table `dossiers`
--

INSERT INTO `dossiers` (`id_dossier`, `date_dossier`, `id_client`, `id_type_contact`, `id_agence`, `id_employe`, `montant_ht`, `id_statut_com`) VALUES
(10900062, '2009-09-01 00:00:00', 1, 10, 49, 13, 30000, 0),
(10900067, '2009-09-01 00:00:00', 2, 20, 49, 13, 5000, 0),
(10900070, '2009-09-01 00:00:00', 4, 20, 49, 21, 40000, 0),
(10900071, '2009-09-01 00:00:00', 3, 20, 49, 13, 5000, 0),
(10900075, '2009-09-01 00:00:00', 5, 20, 59, 3, 150000, 0);

-- --------------------------------------------------------

--
-- Structure de la table `employes`
--

CREATE TABLE IF NOT EXISTS `employes` (
  `id_employe` int(11) NOT NULL DEFAULT '0',
  `nom_employe` varchar(255) DEFAULT NULL,
  `prenom_employe` varchar(255) DEFAULT NULL,
  `id_agence` int(11) DEFAULT NULL,
  `id_fonction` int(11) DEFAULT NULL,
  PRIMARY KEY (`id_employe`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Contenu de la table `employes`
--

INSERT INTO `employes` (`id_employe`, `nom_employe`, `prenom_employe`, `id_agence`, `id_fonction`) VALUES
(3, 'Durand', 'Prénom de Durand', 59, 30),
(13, 'Dupont', 'Prénom de Dupont', 49, 30),
(21, 'Poirier', 'Prénom de Poirier', 49, 30);

-- --------------------------------------------------------

--
-- Structure de la table `fonctions`
--

CREATE TABLE IF NOT EXISTS `fonctions` (
  `id_fonction` int(11) NOT NULL DEFAULT '0',
  `titre_fonction` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id_fonction`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Contenu de la table `fonctions`
--

INSERT INTO `fonctions` (`id_fonction`, `titre_fonction`) VALUES
(30, 'Fonction 30');

-- --------------------------------------------------------

--
-- Structure de la table `taux_com`
--

CREATE TABLE IF NOT EXISTS `taux_com` (
  `id_fonction` int(11) DEFAULT NULL,
  `id_type_contact` int(11) DEFAULT NULL,
  `seuil_min_ca` float DEFAULT NULL,
  `seuil_max_ca` float DEFAULT NULL,
  `taux_com` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Contenu de la table `taux_com`
--

INSERT INTO `taux_com` (`id_fonction`, `id_type_contact`, `seuil_min_ca`, `seuil_max_ca`, `taux_com`) VALUES
(30, 10, 0, 19999, 0),
(30, 10, 20000, 49999, 0.02),
(30, 10, 50000, 1e+012, 0.05),
(30, 20, 0, 19999, 0),
(30, 20, 20000, 49999, 0.04),
(30, 20, 50000, 1e+012, 0.07),
(30, 30, 0, 19999, 0),
(30, 30, 20000, 49999, 0.06),
(30, 30, 50000, 1e+012, 0.08);

-- --------------------------------------------------------

--
-- Structure de la table `type_contact`
--

CREATE TABLE IF NOT EXISTS `type_contact` (
  `id_type_contact` int(11) NOT NULL DEFAULT '0',
  `titre_type_contact` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id_type_contact`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Contenu de la table `type_contact`
--

INSERT INTO `type_contact` (`id_type_contact`, `titre_type_contact`) VALUES
(10, 'Passif RVF'),
(20, 'Actif RVA'),
(30, 'GMS - Salons - Foires');
Ce qui donne le résultat suivant après exécution de notre requête de calcul des taux:

Code : Tout sélectionner

id_employe ca_com taux_com 13 30000 0.02 13 10000 0 21 40000 0.04 3 150000 0.07

Requête complexe avec sous-requêtes

par franck85 » 02 sept. 2009, 13:32

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 ?
p.s.

les autres tables sont :

<gras>table_type_contact</gras>
<code type="html">
id_type_contact type_contact
10 Passif RVF
20 Actif RVA
30 GMS - Salons - Foires
</code>

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