|
-- 1 233 801 mandats (99,99 %) sont conformes.
|
|
-- use AAProd
|
|
|
|
Declare @total_mandat INT;
|
|
SET @total_mandat = (SELECT COUNT(*) FROM mandats);
|
|
|
|
--SELECT GETDATE() as jour, @total_mandat total_mandats, @total_mandat - count(mdt_conseilleruvr) as sans_nego
|
|
-- , ROUND((@total_mandat - count(mdt_conseilleruvr))*100/@total_mandat,4) as pourcentage FROM mandats;
|
|
|
|
SET NOCOUNT ON;
|
|
SELECT ' > mandats sans n?gociateur : '; -- 141
|
|
SET NOCOUNT OFF;
|
|
SELECT * FROM mandats m WHERE mdt_conseilleruvr IS NULL
|
|
order by m.mdt_datemaj DESC;
|
|
-- transactions de ces mandats sans n?gociateur : 7
|
|
SELECT * FROM transactions t WHERE t.Transac_IdMandat in ( SELECT mdt_id FROM mandats m WHERE mdt_conseilleruvr IS NULL)
|
|
order by t.transac_datemaj DESC;
|
|
-- agences concern?es
|
|
SELECT s.suc_idagence [agences concern?es], s.Suc_Code, s.suc_succursale, s.suc_actif,s.Suc_Principale FROM succursales s INNER JOIN (
|
|
SELECT distinct m.mdt_idagence FROM mandats m WHERE mdt_conseilleruvr IS NULL
|
|
UNION SELECT t.transac_idagence FROM transactions t WHERE t.Transac_IdMandat in ( SELECT mdt_id FROM mandats m WHERE mdt_conseilleruvr IS NULL)
|
|
) infra on s.suc_idagence = infra.mdt_idagence and s.Suc_Principale=1 ORDER BY s.suc_idagence
|
|
;
|
|
|
|
-- 99 infractions : 27 mandats sont d?tenus par un confr?re (type 2)
|
|
SET NOCOUNT ON;
|
|
SELECT ' > mandats d?tenus par un confr?re (type 2)';
|
|
SET NOCOUNT OFF;
|
|
SELECT * FROM mandats m INNER JOIN negociateurs n ON m.mdt_conseilleruvr = n.nego_id
|
|
WHERE n.nego_typenego !=1
|
|
order by m.mdt_datemaj DESC;
|
|
SELECT s.suc_idagence [agences concern?es], s.Suc_Code, s.suc_succursale, s.suc_actif,s.Suc_Principale FROM succursales s INNER JOIN (
|
|
SELECT distinct m.mdt_idagence FROM mandats m INNER JOIN negociateurs n ON m.mdt_conseilleruvr = n.nego_id
|
|
WHERE n.nego_typenego !=1
|
|
) infra on s.suc_idagence = infra.mdt_idagence and s.Suc_Principale=1 ORDER BY s.suc_idagence;
|
|
|
|
-- et 72 par un n?gociateur interne d?une autre agence
|
|
SET NOCOUNT ON;
|
|
SELECT ' > mandats d?tenus par un n?gociateur interne d?une autre agence';
|
|
SET NOCOUNT OFF;
|
|
SELECT * FROM mandats m INNER JOIN negociateurs n ON m.mdt_conseilleruvr = n.nego_id
|
|
WHERE n.nego_idagence !=m.mdt_idagence
|
|
order by m.mdt_datemaj DESC;
|
|
SELECT s.suc_idagence [agences concern?es], s.Suc_Code, s.suc_succursale, s.suc_actif,s.Suc_Principale FROM succursales s INNER JOIN (
|
|
SELECT distinct m.mdt_idagence FROM mandats m INNER JOIN negociateurs n ON m.mdt_conseilleruvr = n.nego_id
|
|
WHERE n.nego_idagence !=m.mdt_idagence
|
|
) infra on s.suc_idagence = infra.mdt_idagence and s.Suc_Principale=1 ORDER BY s.suc_idagence;
|
|
|