---

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

---

---

---