https://pastein.ru/t/q7
скопируйте уникальную ссылку для отправки
SET NOCOUNT ON
SET ARITHABORT OFF
SET ARITHIGNORE ON
SET ANSI_WARNINGS OFF
DECLARE @startDate date = DATEADD(DAY, -7, GETDATE());
DECLARE @endDate date = GETDATE();
;with DCH (entityId, entityName, dataProviderId, dataProviderIds) AS (
select entityId, entityName,
CAST(LEFT(dataProviderIds, CHARINDEX(',', dataProviderIds + ',') - 1) AS INT),
STUFF(dataProviderIds, 1, CHARINDEX(',', dataProviderIds + ','), '')
from DataConsumerHistory dch
where dch.startTime >= @startDate and dch.startTime < @endDate and dch.successCount > 0
AND entityName IN ('Lead', 'ServiceLead', 'Contact')
UNION ALL
SELECT
entityId, entityName,
CAST(LEFT(dataProviderIds, CHARINDEX(',', dataProviderIds + ',') - 1) AS INT),
STUFF(dataProviderIds, 1, CHARINDEX(',', dataProviderIds + ','), '')
FROM DCH
WHERE
dataProviderIds > ''
),
DCHP AS (
SELECT entityId, entityName, dataProviderId, dataProviderIds, dp.sourceName AS providerName FROM DCH
LEFT JOIN DataProvider AS DP ON DP.id = DCH.dataProviderId
WHERE dp.name like 'DWC Lead Provider%' or dp.name like 'DWC Contact-Parts-Service Provider%'),
LeadsData AS (
SELECT CAST(Timestamp AS date) AS ldate,
dh.dataProviderIds,
dh.providerName,
Lead.UID,
Lead.LeadOemProgramID,
Lead.Lead_Type,
Lead.isCertified,
UPPER(OEMs.name) as oemName,
CASE WHEN dh.entityName = 'Lead' THEN 1 ELSE 0 END AS isLead,
CASE WHEN dh.entityName = 'ServiceLead' THEN 1 ELSE 0 END AS isServiceLead,
CASE WHEN dh.entityName = 'Contact' THEN 1 ELSE 0 END AS isContact
FROM DCHP dh
JOIN Lead (nolock) ON Lead.UID = dh.entityId
JOIN OEMs (nolock) ON Lead.oemId = OEMs.id
WHERE Timestamp >= @startDate
AND Timestamp < @endDate),
DWCP_IWA_METRICS_PRIORITIZED AS (
select IWA.date,
IWA.MAKE,
IWA.dwpProvider, --Name of the DWP Provider
IWA.siteVisits, --Total Site Visits on that day
IWA.totalVLPViews, --Total Search Results Page Views
IWA.newVehicleVLPViews, --Subset of SRP Page Views WHERE Lead Type is NEW
IWA.totalVDPViews, --Total Vehicle Detail Page Views
IWA.newVehicleVDPViews,
ROW_NUMBER() OVER(PARTITION BY MAKE, dwpProvider, dealerId, date ORDER BY id DESC) AS NR
from DWCP_IWA_METRICS IWA (nolock)
where IWA.date >= @startDate AND IWA.date < @endDate),
DWCP_IWA_METRICS_LATEST AS (
SELECT * FROM DWCP_IWA_METRICS_PRIORITIZED WHERE nr = 1
),
DWCP_DRS_METRICS_PRIORITIZED AS (
select drsTotalLeads, make, provider, dealerId, date,
ROW_NUMBER() OVER(PARTITION BY make, provider, dealerId, date ORDER BY id DESC) as nr
from DWCP_DRS_METRICS d2 (nolock)),
DWCP_DRS_METRICS_LATEST AS (
SELECT * FROM DWCP_DRS_METRICS_PRIORITIZED WHERE nr = 1)
SELECT IWA.date,
IWA.MAKE,
IWA.dwpProvider, --Name of the DWP Provider
IWA.siteVisits, --Total Site Visits on that day
IWA.totalVLPViews, --Total Search Results Page Views
IWA.newVehicleVLPViews, --Subset of SRP Page Views WHERE Lead Type is NEW
IWA.totalVDPViews, --Total Vehicle Detail Page Views
IWA.newVehicleVDPViews, --Subset of all VDPs WHERE the vehicle is NEW
COALESCE(Leads.totalPhoneLeads, 0) AS totalPhoneLeads,
COALESCE(Leads.salesPhoneLeads, 0) AS salesPhoneLeads,
COALESCE(Leads.t, 0) + COALESCE(DRS.drsTotalLeads, 0) + COALESCE(Leads.t2, 0) + COALESCE(Leads.t3, 0) as totalFormLeads,
COALESCE(Leads.t, 0) + COALESCE(DRS.drsTotalLeads, 0) AS newVehicleSalesFormLeads,
COALESCE(Leads.t2, 0) newVehicleSalesFormLeads,
COALESCE(Leads.t3, 0) serviceFormLeads,
COALESCE(Leads.t7, 0) + COALESCE(Leads.t8, 0) AS chatAndSMSSalesLeads,
COALESCE(Leads.t9, 0) + COALESCE(Leads.t10, 0) AS chatAndSMSServiceLeads
FROM DWCP_IWA_METRICS_LATEST IWA (nolock)
LEFT JOIN DWCP_DRS_METRICS_PRIORITIZED DRS (nolock) on IWA.MAKE = DRS.MAKE AND IWA.date = DRS.date AND
IWA.dwpProvider = DRS.provider
OUTER APPLY (
SELECT
SUM(CASE WHEN L.isLead = 1 AND L.LeadOemProgramID in (78739, 78742, 78743, 78746) THEN 1 ELSE 0 END) as totalPhoneLeads,
SUM(CASE WHEN L.isLead = 1 AND L.LeadOemProgramID in (78742, 78746) THEN 1 ELSE 0 END) as salesPhoneLeads,
SUM(CASE WHEN L.isLead = 1 AND L.Lead_Type = 'NEW' AND L.LeadOemProgramID in (78696, 78748, 78686, 78687, 78694, 78751, 78749, 78750, 78759, 78761, 78762, 78764, 78765, 78767) THEN 1 ELSE 0 END) AS t,
SUM(CASE WHEN L.isCertified = 1 AND L.LeadOemProgramID in (78696, 78748, 78686, 78687, 78694, 78751, 78759, 78761, 78762, 78764, 78765, 78767) THEN 1 ELSE 0 END) AS t2,
SUM(CASE WHEN L.isServiceLead = 1 AND L.LeadOemProgramID in (78752, 78753, 78755, 78756, 78757, 78758) THEN 1 ELSE 0 END) AS t3,
SUM(CASE WHEN L.isLead = 1 AND L.LeadOemProgramID in (78722, 78723) THEN 1 ELSE 0 END) AS t7,
SUM(CASE WHEN L.isContact = 1 AND L.LeadOemProgramID in (78726, 78727) THEN 1 ELSE 0 END) AS t8,
SUM(CASE WHEN L.isLead = 1 AND L.LeadOemProgramID in (78724, 78725) THEN 1 ELSE 0 END) AS t9,
SUM(CASE WHEN L.isContact = 1 AND L.LeadOemProgramID in (78728, 78729) THEN 1 ELSE 0 END) AS t10
FROM LeadsData AS L
WHERE L.isLead = 1
AND L.ldate = IWA.date
AND L.oemName = UPPER(IWA.MAKE)
AND L.providerName = IWA.dwpProvider
) AS Leads
option(recompile)
SET ANSI_WARNINGS ON
SET ARITHIGNORE OFF
SET ARITHABORT ON
SET NOCOUNT OFF