Bonjour à tous,
Je ne sais pas écrire la requête sql qui me permettrait d'obtenir l'objectif décrit ci-après.
J'ai 3 tables : contact, demande, commercial définies comme ci-après.
Contexte simplifié : une personne (un contact) fait une ou plusieurs demandes d'information sur internet. Chaque contact est affecté à un commercial.
Je souhaite pouvoir effectuer des statistiques par commercial selon une multitude de critères. Parmi ces critères, il y en a un qui est toujours utilisé c'est la période de temps où une demande a été créée .Je gère dans la table demande , une date de création (date_demande).
Jusque là, aucun problème.
Seulement voilà, une demande a un statut qui peut prendre 3 valeurs : Pending (valeur par défaut), Signed, Lost. Lorsque le statut change, j'enregistre la date dans un champ date_modif_status de la table demande.
Je souhaiterais via une seule requête connaître par commercial toutes les demandes signées (Signed) pour une période définie (en fonction de sa date de création date_demande) mais aussi toutes les demandes signées pour cette même période date_modif_status qui concerne des demande créées avant la période définie.
Ci-dessous définition épurée des 3 tables.
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 -- -- Structure de la table `contact` -- CREATE TABLE `contact` ( `id_contact` int(11) NOT NULL auto_increment, `email1` varchar(250) collate utf8_unicode_ci NOT NULL, `commercial_id` char(3) character set latin1 NOT NULL default 'XXX', `date_creation` datetime NOT NULL default '0000-00-00 00:00:00', `date_last_modif` datetime NOT NULL default '0000-00-00 00:00:00', `cycle` enum('Leads','Client') character set latin1 NOT NULL default 'Leads', PRIMARY KEY (`id_contact`), KEY `email1` (`email1`) ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
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 -- -- Structure de la table `demande` -- CREATE TABLE `demande` ( `date_demande` datetime NOT NULL default '0000-00-00 00:00:00', `type_prestation` enum('NC','B','BS','BSH','LUX') character set latin1 NOT NULL default 'NC', `contact_id` int(11) NOT NULL default '0', `commercial_id` int(11) NOT NULL default '0', `num_dossier` mediumint(5) unsigned zerofill NOT NULL default '00000', `status` enum('Pending','Signed','Lost') character set latin1 NOT NULL default 'Pending', `date_modif_status` date default NULL, PRIMARY KEY (`id_demande`), KEY `contact_id` (`contact_id`), KEY `commercial_id` (`commercial_id`) ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;Aujourd'hui, pour simplifier mon code, en fonction des critères de sélection choisis, je passe par une VIEW intermédiaire v_stats_DirCom.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11 -- -- Structure de la table `commercial` -- CREATE TABLE `commercial` ( `id_commercial` char(3) character set latin1 NOT NULL default '', `prenom_com` varchar(100) collate utf8_unicode_ci NOT NULL, `nom_com` varchar(100) collate utf8_unicode_ci NOT NULL, PRIMARY KEY (`id_commercial`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Exemple : pour toutes les demandes créées en mars 2012
En suite, j'effectue une requête du type :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4 CREATE OR REPLACE VIEW v_stats_DirCom AS SELECT c.commercial_id,com.prenom_com,com.nom_com, com.actif,d.id_demande,d.date_demande,d.status, d.num_dossier,d.contact_id,d.commentaireWI,d.site_source, d.source_lien,d.type_prestation,d.tag_suivi_direction,d.commentaires_relance FROM demande d LEFT JOIN contact c ON d.contact_id = c.id_contact LEFT JOIN commercial com ON c.commercial_id = com.id_commercial WHERE 1 AND YEAR(d.date_demande)='2012' AND MONTH(d.date_demande)='3'
ce qui me donne un tableau du type :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12 SELECT commercial_id,prenom_com,nom_com,COUNT(date_demande) AS Total_a, SUM(IF(num_dossier='99999',1,0)) AS Impossible_b, COUNT(date_demande)-SUM(IF(num_dossier='99999',1,0)) AS Total_c, CEILING( 100*( SUM(IF(num_dossier='99999',1,0))/COUNT(date_demande) ) )AS Taux_99999, SUM(IF(type_prestation='B' AND num_dossier!='99999',1,0)) AS R_BB, SUM(IF( (type_prestation='BS' OR type_prestation='BSH') AND num_dossier!='99999',1,0)) AS R_CW, SUM(IF(type_prestation='LUX' AND num_dossier!='99999',1,0)) AS R_LUX, SUM(IF(type_prestation='NC' AND num_dossier!='99999',1,0)) AS R_NC, SUM(IF(status='Pending' AND num_dossier!='99999',1,0)) AS R_Pending, SUM(IF(status='Signed' AND num_dossier!='99999',1,0)) AS R_SIGNEE FROM v_stats_DirCom GROUP BY commercial_id
Commercial....|...Total..|..Signed..|....critère X..|...critère Z..|
AAA.............|...100....|.........3..|..............0..|..............2..|
BBB.............|....50.... |.........0..|..............9..|..............8..|
CCC.............|....75....|.........1..|..............4..|..............7..|
Je souhaiterai avoir une autre colonne où nous aurions toutes les demandes signées pour mars 2012 : sous entendu YEAR(d.date_modif_status)='2012' AND MONTH(d.date_modif_status)='3' AND d.status=''Signed'.
La difficulté vient du fait que je passe par une requête intermédiaire (VIEW). Cette VIEW me permet de sélectionner ma population mère d'enregistrements en fonction d'une multitude de critère. Une fois ma view créée, je fais mes stats. Je pourrais à priori définir une VIEW qui sélectionnerait toutes mes demandes avec YEAR(d.date_modif_status)='2012' OR (MONTH(d.date_modif_status)='3' AND d.status=''Signed). Mais si je procède ainsi, cela va fausser toutes mes autres stats. Les stats doivent porter sur toutes les demandes pour une période donnée où la date de création est le critère numéro un. Je souhaite seulement pour un critère (status Signed ou pas) avoir en plus le nombre de demandes signées pour cette même période définie (prise en compte du MONTH(d.date_modif_status)='3' AND d.status=''Signed).
Est-ce possible ?
Par avance merci.
tavar
Partager