La colonne DATECREATION_LEAD fait bien partie de ma table TRESUL, mais TRESUL est un rassemblement de d'autre table TRESUL correspond à ma table CIBLAGE à la fin Les voici : PROC SQL;
CREATE TABLE LISTE_LEADS AS
SELECT DISTINCT
TCAMP.*,
TLEAD.PARENTID,
TLEAD.UTILISATEURID,
TLEAD.PORTEFEUILLEID,
TLEAD.IDLEAD,
TLEAD.REFERENCE,
TLEAD.NOMBREESCALADE,
TLEAD.DATECREATION AS DATECREATION_LEAD,
TLEAD.DATEMODIFICATION AS DATEMODIFICATION_LEAD,
TLEAD.STATUT AS STATUT_LEAD,
TLEAD.ABANDONREFUSID,
TLEAD.ABANDONMOTIF AS COMMENTAIRE
FROM WORK.CAMPAGNE_LEADS TCAMP
LEFT JOIN INFOCE.INFO_SESAME_CAMP_LEAD TLEAD ON (TCAMP.IDCAMPAGNE = TLEAD.CAMPAGNEID)
WHERE TLEAD.DATECREATION >= "01JAN2020:00:00:00"dt /* DATE CORRESPONDANT AU DEBUT DU SUIVI LEADS PAR RAPPORT A LA CREATION DU LEAD*/
;
QUIT;
PROC SQL;
CREATE TABLE LISTE_LEADS AS
SELECT DISTINCT
TLEAD.*,
THOFF.OFFREID,
TOFF.LIBELLE AS FAMILLE_OFFRE
FROM WORK.LISTE_LEADS TLEAD
LEFT JOIN INFOCE.INFO_SESAME_CAMP_LEAD_H_OFFRE THOFF ON (THOFF.LEADID = TLEAD.IDLEAD)
LEFT JOIN INFOCE.INFO_SESAME_CAMP_LEAD_OFFRE TOFF ON (THOFF.OFFREID = TOFF.IDOFFRE)
;
QUIT;
PROC SQL;
CREATE TABLE LISTE_LEADS AS
SELECT DISTINCT
TLEAD.*,
TREFUS.LIBELLE AS RAISON_ABANDON
FROM WORK.LISTE_LEADS TLEAD
LEFT JOIN INFOCE.INFO_SESAME_CAMP_REFUS TREFUS ON (TLEAD.ABANDONREFUSID = TREFUS.IDREFUS)
;
QUIT;
/* AJOUT INFOS SUR LA SOCIETE PORTEUSE DU LEAD */
PROC SQL;
CREATE TABLE SOCIETE AS
SELECT DISTINCT
TSOC.IDSOCIETE,
TSOC.SIREN,
TSOC.RAISONSOCIALE,
TSOC.EFFECTIFSFR,
TSOC.SEGMENT,
TSOC.SEGMENTSFR
FROM WORK.LISTE_LEADS TLEAD
LEFT JOIN INFOCE.INFO_SESAME_SOCIETE TSOC ON (TLEAD.PARENTID = TSOC.IDSOCIETE)
;
QUIT;
/* AJOUTS DES INFORMATIONS SUR LE PROPRIETAIRE DU LEAD */
PROC SQL;
CREATE TABLE UTILISATEUR AS
SELECT DISTINCT
TLEAD.UTILISATEURID,
TUSER.IDUTILISATEUR,
TUSER.ACTIF,
TUSER.UPERID,
CATS(TUSER.NOM,"_",TUSER.PRENOM) AS PROPRIETAIRE_LEAD,
TUSER.DISTRIBUTEURID,
TUSEREX.CODEPDV,
TUSEREX.NOMPDV AS PDV_EXTERNE,
TUSEREX.ENSEIGNE AS ENSEIGNE_EXTERNE,
TUSEREX.LIBELLEREGIONSFR AS REGION_EXTERNE,
TUSERIN.SECTEUR_RATTACHEMENT AS PDV_INTERNE,
CASE WHEN TUSER.ACTIF = "T" THEN "ACTIF" ELSE "compte non sectorisé" END AS UTILISATEUR_ACTIF
FROM WORK.LISTE_LEADS TLEAD
LEFT JOIN INFOCE.INFO_SESAME_UTILISATEUR TUSER ON (TLEAD.UTILISATEURID = TUSER.IDUTILISATEUR)
LEFT JOIN INFOCE.INFO_SESAME_UTIL_SEC_EXTER TUSEREX ON (TUSER.IDUTILISATEUR = TUSEREX.IDUTILISATEUR)
LEFT JOIN INFOCE.INFO_SESAME_UTIL_SEC_INTER TUSERIN ON (TUSER.IDUTILISATEUR = TUSERIN.UTILISATEURID)
;
QUIT;
/* COMPILATION DES PROGRAMMES CI DESSUS */
PROC SQL;
CREATE TABLE CIBLAGE AS
SELECT DISTINCT
TLEAD.*,
TSOC.*,
TUTIL.*
FROM WORK.LISTE_LEADS TLEAD
LEFT JOIN WORK.SOCIETE TSOC ON (TLEAD.PARENTID = TSOC.IDSOCIETE)
LEFT JOIN WORK.UTILISATEUR TUTIL ON (TLEAD.UTILISATEURID = TUTIL.IDUTILISATEUR)
;
QUIT;
... View more