1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
|
SELECT ISNULL(COUNT(DISTINCT T1.Email),0) AS DoublonsPart , T1.Code_Ope
FROM (
SELECT A1.Email, A1.Code_Ope
FROM CTE_LEADS_ALL AS A1
LEFT JOIN (
SELECT DISTINCT S1.code_ope
FROM Advertising.dbo.Lead_Minisite AS S1 WITH (NOLOCK)
INNER JOIN UNICITE_GLOBALE.dbo.Campagne AS S2 WITH (NOLOCK) ON S2.Code = S1.Code_Ope
WHERE S2.Id_Partenaire IN (
SELECT DISTINCT R1.Id_Partenaire
FROM UNICITE_GLOBALE.dbo.Campagne AS R1 WITH (NOLOCK)
INNER JOIN UNICITE_GLOBALE.dbo.Partenaire AS R2 WITH (NOLOCK) ON R2.Id_Partenaire = R1.Id_Partenaire
INNER JOIN CTE_REGLES AS R3 ON R1.Code = R3.Code_campagne
)
) AS A2 ON A2.Code_Ope = A1.Code_Ope
INNER JOIN (
SELECT Q1.Email, Q1.Date_Creation, Q2.Code_Ope
FROM advertising.dbo.Email_Adresse AS Q1 WITH (NOLOCK)
INNER JOIN Advertising.dbo.Lead_Minisite AS Q2 WITH (NOLOCK) ON Q2.Email = Q1.Email
AND CONVERT(DATETIME,Q2.Creation_Date,121) = Q1.Date_Creation
) AS A3 ON A1.Email = A3.Email AND A1.Creation_Date > A3.Date_Creation
) AS T1
GROUP BY T1.Code_Ope |
Partager