---

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

---

---

---