https://pastein.ru/t/h7
скопируйте уникальную ссылку для отправки
Загрузка данных
SET NOCOUNT ON
SET ARITHABORT OFF
SET ARITHIGNORE ON
SET ANSI_WARNINGS OFF
DROP TABLE IF EXISTS #iwa_data_consumer_history;
DROP TABLE IF EXISTS #iwa_leads;
DROP TABLE IF EXISTS #iwa_service_lead;
DROP TABLE IF EXISTS #iwa_contact_lead;
-- period
DECLARE @startDate date = DATEADD(DAY, -7, GETDATE());
DECLARE @endDate date = GETDATE();
-- temp tables for leads accepted FROM IWA providers
SELECT *
INTO #iwa_data_consumer_history
FROM (SELECT dch.*, dp.sourceName as providerName
FROM DataProvider dp WITH (NOLOCK)
JOIN DataConsumerHistory dch WITH (NOLOCK) ON dp.id in (SELECT convert(int, value) from string_split(dch.dataProviderIds, ','))
and dch.startTime >= @startDate and dch.startTime < @endDate and dch.successCount > 0
WHERE dp.name like 'DWC Lead Provider%' or dp.name like 'DWC Contact-Parts-Service Provider%') as sq;
SELECT *
INTO #iwa_leads
FROM (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
FROM #iwa_data_consumer_history dh
JOIN Lead (nolock) ON Lead.UID = dh.entityId and dh.entityName = 'Lead'
JOIN OEMs (nolock) ON Lead.oemId = OEMs.id
WHERE Timestamp >= @startDate
AND Timestamp < @endDate) AS sq
SELECT *
INTO #iwa_service_lead
FROM (SELECT CAST(Timestamp AS date) AS ldate,
dh.dataProviderIds,
dh.providerName,
ServiceLead.UID,
ServiceLead.LeadOemProgramID,
ServiceLead.Lead_Type,
UPPER(OEMs.name) as oemName
FROM #iwa_data_consumer_history dh
JOIN ServiceLead (nolock) ON ServiceLead.UID = dh.entityId and dh.entityName = 'ServiceLead'
JOIN OEMs (nolock) ON ServiceLead.oemId = OEMs.id
WHERE Timestamp >= @startDate
AND Timestamp < @endDate) AS sq
SELECT *
INTO #iwa_contact_lead
FROM (SELECT CAST(Timestamp AS date) AS ldate,
dh.dataProviderIds,
dh.providerName,
Contact.UID,
Contact.LeadOemProgramID,
Contact.Lead_Type,
UPPER(OEMs.name) as oemName
FROM #iwa_data_consumer_history dh
JOIN Contact (nolock) ON Contact.UID = dh.entityId and dh.entityName = 'Contact'
JOIN OEMs (nolock) ON Contact.oemId = OEMs.id
WHERE Timestamp >= @startDate
AND Timestamp < @endDate) AS sq
-- IWA Outgoing DWCP Feed
-- https://docs.google.com/spreadsheets/d/1uN3JvTbuXVhRKgKsUpb54nIMamfCttSN-i11WD4dxlY/edit?pli=1#gid=1622280470
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
(SELECT count(*)
FROM #iwa_leads
WHERE #iwa_leads.ldate = IWA.date
AND #iwa_leads.oemName = UPPER(IWA.MAKE)
AND #iwa_leads.providerName = IWA.dwpProvider
AND #iwa_leads.LeadOemProgramID in (78739, 78742, 78743, 78746)
) AS totalPhoneLeads,
(SELECT count(*)
FROM #iwa_leads
WHERE #iwa_leads.ldate = IWA.date
AND #iwa_leads.oemName= UPPER(IWA.MAKE)
AND #iwa_leads.providerName = IWA.dwpProvider
AND #iwa_leads.LeadOemProgramID in (78742, 78746)
) AS salesPhoneLeads,
(SELECT count(*)
FROM #iwa_leads
WHERE #iwa_leads.ldate = IWA.date
AND #iwa_leads.oemName = UPPER(IWA.MAKE)
AND #iwa_leads.Lead_Type = 'NEW'
AND #iwa_leads.providerName = IWA.dwpProvider
AND #iwa_leads.LeadOemProgramID in
(78696, 78748, 78686, 78687, 78694, 78751, 78749, 78750, 78759, 78761, 78762, 78764, 78765, 78767)
) + COALESCE(DRS.drsTotalLeads, 0) +
(SELECT count(*)
FROM #iwa_leads
WHERE #iwa_leads.ldate = IWA.date
AND #iwa_leads.oemName = UPPER(IWA.MAKE)
AND #iwa_leads.isCertified = 1
AND #iwa_leads.providerName = IWA.dwpProvider
AND #iwa_leads.LeadOemProgramID in
(78696, 78748, 78686, 78687, 78694, 78751, 78759, 78761, 78762, 78764, 78765, 78767)) +
(SELECT count(*)
FROM #iwa_service_lead
WHERE #iwa_service_lead.ldate = IWA.date
AND #iwa_service_lead.oemName = UPPER(IWA.MAKE)
AND #iwa_service_lead.providerName = IWA.dwpProvider
AND #iwa_service_lead.LeadOemProgramID in
(78752, 78753, 78755, 78756, 78757, 78758)) AS totalFormLeads,
(SELECT count(*)
FROM #iwa_leads
WHERE #iwa_leads.ldate = IWA.date
AND #iwa_leads.oemName = UPPER(IWA.MAKE)
AND #iwa_leads.Lead_Type = 'NEW'
AND #iwa_leads.providerName = IWA.dwpProvider
AND #iwa_leads.LeadOemProgramID in
(78696, 78748, 78686, 78687, 78694, 78751, 78749, 78750, 78759, 78761, 78762, 78764, 78765, 78767)
) + COALESCE(DRS.drsTotalLeads, 0) AS newVehicleSalesFormLeads,
(SELECT count(*)
FROM #iwa_leads
WHERE #iwa_leads.ldate = IWA.date
AND #iwa_leads.oemName = UPPER(IWA.MAKE)
AND #iwa_leads.isCertified = 1
AND #iwa_leads.providerName = IWA.dwpProvider
AND #iwa_leads.LeadOemProgramID in
(78696, 78748, 78686, 78687, 78694, 78751, 78759, 78761, 78762, 78764, 78765, 78767)
) AS newVehicleSalesFormLeads,
(SELECT count(*)
FROM #iwa_service_lead
WHERE #iwa_service_lead.ldate = IWA.date
AND #iwa_service_lead.oemName = UPPER(IWA.MAKE)
AND #iwa_service_lead.providerName = IWA.dwpProvider
AND #iwa_service_lead.LeadOemProgramID in (78752, 78753, 78755, 78756, 78757, 78758)
) AS serviceFormLeads,
(SELECT count(*)
FROM #iwa_leads
WHERE #iwa_leads.ldate = IWA.date
AND #iwa_leads.oemName = UPPER(IWA.MAKE)
AND #iwa_leads.providerName = IWA.dwpProvider
AND #iwa_leads.LeadOemProgramID in (78722, 78723)
) + (SELECT count(*)
FROM #iwa_contact_lead
WHERE #iwa_contact_lead.ldate = IWA.date
AND #iwa_contact_lead.oemName = UPPER(IWA.MAKE)
AND #iwa_contact_lead.providerName = IWA.dwpProvider
AND #iwa_contact_lead.LeadOemProgramID in (78726, 78727)
) AS chatAndSMSSalesLeads,
(SELECT count(*)
FROM #iwa_leads
WHERE #iwa_leads.ldate = IWA.date
AND #iwa_leads.oemName = UPPER(IWA.MAKE)
AND #iwa_leads.providerName = IWA.dwpProvider
AND #iwa_leads.LeadOemProgramID in (78724, 78725)
) + (SELECT count(*)
FROM #iwa_contact_lead
WHERE #iwa_contact_lead.ldate = IWA.date
AND #iwa_contact_lead.oemName = UPPER(IWA.MAKE)
AND #iwa_contact_lead.providerName = IWA.dwpProvider
AND #iwa_contact_lead.LeadOemProgramID in (78728, 78729)
) AS chatAndSMSServiceLeads
FROM DWCP_IWA_METRICS IWA (nolock)
LEFT JOIN DWCP_DRS_METRICS DRS (nolock)
on IWA.MAKE = DRS.MAKE AND IWA.date = DRS.date AND IWA.dwpProvider = DRS.provider
WHERE IWA.date >= @startDate
AND IWA.date < @endDate
and IWA.id = (select max(i2.id)
from DWCP_IWA_METRICS i2 (nolock)
where i2.MAKE = IWA.MAKE
and i2.dwpProvider = IWA.dwpProvider
and i2.dealerId = IWA.dealerId
and i2.date = IWA.date)
and (DRS.id is null or DRS.id = (select max(d2.id)
from DWCP_DRS_METRICS d2 (nolock)
where d2.MAKE = DRS.MAKE
and d2.provider = DRS.provider
and d2.dealerId = DRS.dealerId
and d2.date = DRS.date))
SET ANSI_WARNINGS ON
SET ARITHIGNORE OFF
SET ARITHABORT ON
SET NOCOUNT OFF