SELECT [nego_id]      ,[nego_datemaj]      ,[nego_idagence]
      ,[nego_code]      ,[nego_typenego]
      ,[nego_succursale]      ,[nego_nom]
      ,[nego_prenom]      ,[nego_actif]
      ,[ancid]
  FROM [AADev].[dbo].[negociateurs] n
  Where n.nego_typenego=1
  -- AND nego_code='VWA'
  AND ( [nego_id] not in (select u.util_idnego from utilisateurs u where u.util_idnego is not null)
    -- OR n.ancid IS NOT NULL
    );
select * from negociateurs where nego_id in (69894,72743,81590,97978, 1488);
select u.util_idagence, u.util_nom, u.util_prenom, u.util_idnego, neg_sans_util.*
    FROM utilisateurs u INNER JOIN
(
SELECT [nego_id]      ,[nego_datemaj]      ,[nego_idagence]
      ,[nego_code]      ,[nego_typenego]
      ,[nego_succursale]      ,[nego_nom]
      ,[nego_prenom]      ,[nego_actif]
      ,[ancid]
  FROM [AADev].[dbo].[negociateurs] n
  Where n.nego_typenego=1
  -- AND nego_code='VWA'
  AND ( [nego_id] not in (select u.util_idnego from utilisateurs u where u.util_idnego is not null)
    -- OR n.ancid IS NOT NULL
    )
  ) neg_sans_util
    ON u.util_idagence = neg_sans_util.nego_idagence 
WHERE u.util_nom = neg_sans_util.nego_nom AND LEFT(u.util_prenom,1) = LEFT(neg_sans_util.nego_prenom,1);


SELECT count(*)  from negociateurs n 
WHERE n.nego_typenego = 1 -- 13695 en tout, pas dans mandats_nego ni util actifs
and n.nego_actif=0 -- 13576 inactifs
AND n.nego_id not in (select mn.com_idnego from mandats_nego mn where mn.com_idnego IS NOT NULL)
 AND n.nego_id not in (select u.util_idnego from utilisateurs u where u.util_idnego IS NOT NULL AND u.util_actif=1)
 AND n.nego_id not in (SELECT m.mdt_conseilleruvr FROM mandats m WHERE mdt_conseilleruvr IS NOT NULL) -- 13431 inactifs, pas dans mandats
 AND n.nego_id not in (SELECT m.Lot_IdNego FROM Lots m WHERE Lot_IdNego IS NOT NULL) -- 13426 inactifs, pas dans mandats, ni lots
 AND n.nego_id not in (SELECT m.ges_conseilleruvr FROM mandats_gestion m WHERE ges_conseilleruvr IS NOT NULL) -- 13270 inactifs, ni mandats gestion
 AND n.nego_id not in (SELECT m.LGe_IdNego FROM LotGestions m WHERE LGe_IdNego IS NOT NULL) -- 13269 ni LotGestions
 AND n.nego_id not in (SELECT m.Syn_IdNego FROM Syndic m WHERE Syn_IdNego IS NOT NULL) -- 13197 ni Syndic
 AND n.nego_id not in (SELECT m.Vir_IdNego FROM Virements m WHERE Vir_IdNego IS NOT NULL) -- 13179 Virements
;