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