sql_lead_analysis.sql

SELECT
*
FROM
dbo. STATUS;




SELECT
leads.Ref,
MIN(sh.UpdatedDate) AS timestamp,
statuses.Name
FROM
dbo. StatusHistory AS sh
LEFT JOIN dbo. LEAD AS leads ON sh.LeadID = leads.ID
LEFT JOIN dbo. STATUS AS statuses ON statuses.ID = sh.StatusID
WHERE
statuses.Name NOT IN(
    'Policy Live',
    'Policy Cancelled',
    'Policy NTU',
    'One Off Appliance',
    'One Off Boiler/Gas',
    'One Off Car',
    'Cancelled by Insurer',
    'Carlo Processsing',
    'HR360 OnDemand',
    'Carlo Shipped',
    'Staywarm Warranty',
    'Carlo Sold',
    'Carlo Pending Live',
    'Policy MTA',
    'No Quote'
) --AND sh. LeadID = '2C22569D-30E0-419C-B9CC-16B4A128355D'
GROUP BY
leads.Ref,
statuses.Name
ORDER BY
timestamp DESC;


SELECT
sh.LeadID,
sh.UpdatedDate AS timestamp,
statuses.Name
FROM
dbo. StatusHistory AS sh
LEFT JOIN dbo. LEAD AS leads ON sh.LeadID = leads.ID
LEFT JOIN dbo. STATUS AS statuses ON statuses.ID = sh.StatusID
WHERE
--statuses. Name = 'Policy Cancelled'
--AND
sh.LeadID = '9C24EF01-053F-4A18-BCF8-ED11112D5F19'
ORDER BY
timestamp DESC;




SELECT
COUNT(*) AS counting,
sh.LeadID
FROM
dbo. StatusHistory AS sh
LEFT JOIN dbo. LEAD AS leads ON sh.LeadID = leads.ID
LEFT JOIN dbo. STATUS AS statuses ON statuses.ID = sh.StatusID
WHERE
statuses.Name = 'Cancelled by Insurer'
GROUP BY
sh.LeadID
ORDER BY
counting DESC;
SELECT
leads.ID,
leads.CreatedDate,
leads.Ref,
leads.FirstName,
leads.LastName,
leads.PolicyValue,
leads.StatusDate,
leads.FirstTimePolicyLiveDate,
leads.ReinstateDate,
statuses.Name,
statuses.MISGrouping,
leads.DirectDebitDate,
leads.InceptionDate,
leads.FirstTimePolicyLiveDate,
leads.CoverEndDate,
leads.CancelDate,
teams.Name,
psq.Name,
pg.PolicyDocumentTitle,
users.FirstName AS 'Sale User First Name',
users.LastName AS 'Sale User Last Name'
FROM
dbo. LEAD AS leads
LEFT JOIN dbo. STATUS AS statuses ON leads.StatusID = statuses.ID
LEFT JOIN dbo. Team AS teams ON leads.TeamID = teams.ID
LEFT JOIN dbo. PaymentShieldQuotes AS psq ON psq.LeadID = leads.ID
LEFT JOIN dbo. Product_Groups AS pg ON pg.ID = psq.GroupID
LEFT JOIN dbo. Users AS users ON leads.SalesUserId = users.ID WHERE psq. Selected = 1;
--AND MISGrouping = 'Live' --AND
--pg. PolicyDocumentTitle is null
--- policy status change count grouped by year





SELECT
leads.Ref,
statuses.Name,
Count(*),
datepart(yyyy, sh.UpdatedDate)
FROM
dbo. StatusHistory AS sh
LEFT JOIN dbo. LEAD AS leads ON sh.LeadID = leads.ID
LEFT JOIN dbo. STATUS AS statuses ON statuses.ID = sh.StatusID --WHERE statuses. Name = 'Policy Cancelled'
group by
leads.Ref,
statuses.Name,
datepart(yyyy, sh.UpdatedDate) 
-- No Qoute Selected Leads
SELECT
leads.ID,
leads.CreatedDate,
leads.Ref,
leads.FirstName,
leads.LastName,
leads.PolicyValue,
leads.StatusDate,
leads.FirstTimePolicyLiveDate,
leads.ReinstateDate,
statuses.Name,
statuses.MISGrouping,
leads.DirectDebitDate,
leads.InceptionDate,
leads.FirstTimePolicyLiveDate,
leads.CoverEndDate,
leads.CancelDate,
teams.Name,
users.FirstName AS 'Sale User First Name',
users.LastName AS 'Sale User Last Name'
FROM
dbo.LEAD AS leads
LEFT JOIN dbo.STATUS AS statuses ON leads.StatusID = statuses.ID
LEFT JOIN dbo.Team AS teams ON leads.TeamID = teams.ID
LEFT JOIN dbo.Users AS users ON leads.SalesUserId = users.ID
WHERE
leads.ID NOT IN (
    SELECT
     leads.ID
    FROM
     dbo.LEAD AS leads
     LEFT JOIN dbo.PaymentShieldQuotes AS psq ON psq.LeadID = leads.ID
    WHERE
     psq.Selected = 1
);





SELECT
DISTINCT(leads.Ref),
leads.Postcode,
leads.FirstName,
leads.LastName,
leads.CreatedDate,
pg.PolicyDocumentTitle,
leads.PolicyValue,
leads.FirstTimePolicyLiveDate,
statuses.Name as 'StatusName',
leads.StatusDate,
leads.FirstTimePolicyLiveDate,
leads.CancelDate
FROM
dbo. LEAD AS leads
INNER JOIN dbo. STATUS AS statuses ON leads.StatusID = statuses.ID
INNER JOIN dbo. PaymentShieldQuotes AS psq ON psq.LeadID = leads.ID
INNER JOIN dbo. Product_Groups AS pg ON pg.ID = psq.GroupID
INNER JOIN dbo.ClientQuoteProduct as cqp ON psq.ID = cqp.QuoteID
INNER JOIN dbo.NewProducts as newproducts ON cqp.ProductID = newproducts.ID
WHERE psq. Selected = 1 AND statuses.Name ='Policy Live' AND newproducts.MasterGroupID = 1 ;