-- 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;