--------------------------------------------------------------------------
--	1.	Liste des clients et prospects - Feuille 2 TOUS CONTACTS	 	--
--------------------------------------------------------------------------

SELECT DISTINCT 
CLI.TIERS, 'CLIENT', CLI.EMAIL, CLI.EMAILCONTACT, CLI.RUE, CLI.LOC, CLI.CPOSTAL, CLI.VIL, CLI."RAISON SOCIAL CLIENT", CLI.NOM, CLI.TITRELIBELLE, CLI.PRENOM, CLI.NOMCOMTACT, CLI.FONCTION1, CLI.REPR_0001
FROM CARTOFLEX.dbo.CARTOFLEX_CONTACTS_CLIENT_V CLI 
WHERE CLI.DOS='1' 
AND (CLI.REPR_0001 = 'ACS') 
AND (CLI.EMAIL <> '' OR CLI.EMAILCONTACT <> '') 
UNION
SELECT DISTINCT 
PRO.TIERS, 'PROSPECT', PRO.EMAIL, PRO.EMAILCONTACT, PRO.RUE, PRO.LOC, PRO.CPOSTAL, PRO.VIL, PRO."RAISON SOCIAL PROSPECT", PRO.NOM, PRO.TITRELIBELLE, PRO.PRENOM, PRO.NOMCOMTACT, PRO.FONCTION1, PRO.REPR_0001 
FROM CARTOFLEX.dbo.CARTOFLEX_CONTACTS_PROSPECT_V PRO 
WHERE PRO.DOS='1' 
AND (PRO.REPR_0001 = 'ACS') 
AND (PRO.EMAIL <> '' OR PRO.EMAILCONTACT <> '') 

--------------------------------------------------------------------------
--	2.	Retirer les derniers mouvements - Feuille 3					 	--
--------------------------------------------------------------------------

SELECT DISTINCT MOUV.TIERS 
FROM MOUV MOUV 
WHERE (MOUV.DVDT>'2023-01-01' And MOUV.DVDT<'2023-03-02') 


--------------------------------------------------------------------------
--	3.	Matcher les emails											 	--
--------------------------------------------------------------------------

Feuille 2 :
	1. Mettre à vide le champ email [C]
	=SI(ESTVIDE(C2);D2;C2)

Feuille 1 :
	=SI(ESTERREUR(RECHERCHEV(A2;'tous contacts'!A:N;3;FAUX));"";RECHERCHEV(A2;'tous contacts'!A:N;3;FAUX))










----------------------------------------------------------
--	Ne pas utiliser - Affiche les mouvements en cours 	--
----------------------------------------------------------

SELECT DISTINCT 
CLI.TIERS, CLI.EMAIL, CLI.EMAILCONTACT, CLI.RUE, CLI.LOC, CLI.CPOSTAL, CLI.VIL, CLI."RAISON SOCIAL CLIENT", CLI.NOM, CLI.TITRELIBELLE, CLI.PRENOM, CLI.NOMCOMTACT, CLI.REPR_0001 
FROM CARTOFLEX.dbo.CARTOFLEX_CONTACTS_CLIENT_V CLI, MOUV MOUV 
WHERE CLI.DOS='1' 
AND CLI.TIERS = MOUV.TIERS 
AND CLI.REPR_0001 = 'ACS' 
AND (MOUV.DVDT>'2023-01-01' And MOUV.DVDT<'2023-03-02') 

UNION 

SELECT DISTINCT 
PRO.TIERS, PRO.EMAIL, PRO.EMAILCONTACT, PRO.RUE, PRO.LOC, PRO.CPOSTAL, PRO.VIL, PRO."RAISON SOCIAL PROSPECT", PRO.NOM, PRO.TITRELIBELLE, PRO.PRENOM, PRO.NOMCOMTACT, PRO.REPR_0001 
FROM CARTOFLEX.dbo.CARTOFLEX_CONTACTS_PROSPECT_V PRO, MOUV MOUV 
WHERE PRO.DOS='1' 
AND PRO.TIERS = MOUV.TIERS 
AND PRO.REPR_0001 = 'ACS' 
AND (MOUV.DVDT>'2023-01-01' And MOUV.DVDT<'2023-03-02')