TV 2.0: Lead Retainer Score Fix Retainer Status not matching Score

[jesusitd]

The SQL code for the grouping Retainer Status is calculating a MAX over a Text field instead of the Retainer Score:

Solution that must be installed on LeadRetainerStatus query:

CASE
        MAX(`Score`)
        WHEN -1 THEN 'Cancelled'
        WHEN 1 THEN 'Sending'
        WHEN 2 THEN 'Sent'
        WHEN 3 THEN 'Viewed'
        WHEN 4 THEN 'Pending'
        WHEN 5 THEN 'Complete'
        ELSE NULL
    END AS `Retainer Status`

[jesusitd]

@CesarITD can you please install the SQL fix above to TV 2.0 LeadRetainerStatus SQL-PowerQuery?

[CesarITD]

Applied in LeadsRetainerScores Query:

SELECT
    `Lead ID`
    ,CASE
        MAX(`Score`)
        WHEN -1 THEN 'Cancelled'
        WHEN 1 THEN 'Sending'
        WHEN 2 THEN 'Sent'
        WHEN 3 THEN 'Viewed'
        WHEN 4 THEN 'Pending'
        WHEN 5 THEN 'Complete'
        ELSE NULL
    END AS `Retainer Status`
    , MAX(`Score`) AS `Retainer Score`
FROM
    (
        SELECT
            documents.status_id AS 'Status'
            ,
            CASE
                documents.status_id
                WHEN 'Cancelled' THEN -1
                WHEN 'Sending' THEN 1
                WHEN 'Sent' THEN 2
                WHEN 'Viewed' THEN 3
                WHEN 'Pending' THEN 4
                WHEN 'Complete' THEN 5
                ELSE NULL
            END AS 'Score'

            ,documents.status_id retainer_status

            , leads_documents_1_c.leads_documents_1leads_ida AS 'Lead ID'
        FROM
            documents
        JOIN documents_cstm ON
            documents.id = documents_cstm.id_c
        JOIN aos_pdf_templates ON
            documents_cstm.aos_pdf_templates_id_c = aos_pdf_templates.id
            AND aos_pdf_templates.deleted = 0
        JOIN aos_pdf_templates_cstm ON
            aos_pdf_templates.id = aos_pdf_templates_cstm.id_c
            AND LEFT(
                aos_pdf_templates_cstm.template_type_c
                , 8
            ) = 'retainer'
            AND aos_pdf_templates.deleted = 0
        JOIN leads_documents_1_c ON
            documents.id = leads_documents_1_c.leads_documents_1documents_idb
        JOIN leads ON
            leads_documents_1_c.leads_documents_1leads_ida = leads.id
            AND leads.deleted = 0
        JOIN leads_cstm ON
            leads.id = leads_cstm.id_c
        WHERE
            documents.deleted = 0
            AND documents.status_id NOT IN (
                'Error'
                , 'not_started'
            )
    ) d
GROUP BY
    `Lead ID`;