CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `aerion`@`localhost` 
    SQL SECURITY DEFINER
VIEW `vw_consult_dyn1` AS
    SELECT 
        `a`.`prosp_consult_id` AS `prosp_consult_id`,
        `a`.`prosp_first_name` AS `prosp_first_name`,
        `a`.`prosp_last_name` AS `prosp_last_name`,
        '' AS `nickname`,
        `a`.`gender` AS `gender`,
        `a`.`emp_assigned_id` AS `emp_assigned_id`,
        DATE_FORMAT(`a`.`call_recv_dt`, '%m-%d-%y') AS `mod_call_recv_dt`,
        `a`.`call_recv_dt` AS `call_recv_dt`,
        DATE_FORMAT(`a`.`next_call_dt`, '%m-%d-%y') AS `mod_next_call_dt`,
        `a`.`next_call_dt` AS `next_call_dt`,
        `a`.`call_note` AS `call_note`,
        `a`.`prosp_summary` AS `prosp_summary`,
        `a`.`last_updated` AS `last_updated`,
        CONCAT((SELECT 
                        CONCAT(CONVERT( DATE_FORMAT(`a`.`last_updated`, '%m-%d-%y') USING LATIN1),
                                    '
',
                                    `emp`.`firstname`,
                                    _LATIN1' ',
                                    `emp`.`lastname`) AS `updater`
                    FROM
                        `emp`
                    WHERE
                        (`emp`.`emp_id` = IF((`a`.`emp` = 0), 1, `a`.`emp`)))) AS `mod_last_updated`,
        `a`.`prosp_level` AS `prosp_level`,
        (SELECT 
                `look_values`.`look_values`
            FROM
                `look_values`
            WHERE
                (`look_values`.`look_values_id` = `a`.`prosp_level`)) AS `prosp_lvl_desc`,
        `c`.`group_contact` AS `contacts`,
        `d`.`tags` AS `prosp_tag`,
        `e`.`file_group` AS `file_group`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '258' THEN 1
            ELSE 0
        END)) AS `258`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '1358' THEN 1
            ELSE 0
        END)) AS `1358`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '1369' THEN 1
            ELSE 0
        END)) AS `1369`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '1666' THEN 1
            ELSE 0
        END)) AS `1666`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '259' THEN 1
            ELSE 0
        END)) AS `259`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '67' THEN 1
            ELSE 0
        END)) AS `67`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '1707' THEN 1
            ELSE 0
        END)) AS `1707`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '262' THEN 1
            ELSE 0
        END)) AS `262`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '263' THEN 1
            ELSE 0
        END)) AS `263`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '1022' THEN 1
            ELSE 0
        END)) AS `1022`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '260' THEN 1
            ELSE 0
        END)) AS `260`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '261' THEN 1
            ELSE 0
        END)) AS `261`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '289' THEN 1
            ELSE 0
        END)) AS `289`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '290' THEN 1
            ELSE 0
        END)) AS `290`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '291' THEN 1
            ELSE 0
        END)) AS `291`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '293' THEN 1
            ELSE 0
        END)) AS `293`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '297' THEN 1
            ELSE 0
        END)) AS `297`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '1405' THEN 1
            ELSE 0
        END)) AS `1405`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '255' THEN 1
            ELSE 0
        END)) AS `255`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '256' THEN 1
            ELSE 0
        END)) AS `256`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '295' THEN 1
            ELSE 0
        END)) AS `295`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '296' THEN 1
            ELSE 0
        END)) AS `296`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '975' THEN 1
            ELSE 0
        END)) AS `975`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '974' THEN 1
            ELSE 0
        END)) AS `974`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '973' THEN 1
            ELSE 0
        END)) AS `973`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '1417' THEN 1
            ELSE 0
        END)) AS `1417`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '1418' THEN 1
            ELSE 0
        END)) AS `1418`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '1678' THEN 1
            ELSE 0
        END)) AS `1678`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '1680' THEN 1
            ELSE 0
        END)) AS `1680`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '1706' THEN 1
            ELSE 0
        END)) AS `1706`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '1682' THEN 1
            ELSE 0
        END)) AS `1682`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '1684' THEN 1
            ELSE 0
        END)) AS `1684`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '1686' THEN 1
            ELSE 0
        END)) AS `1686`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '1687' THEN 1
            ELSE 0
        END)) AS `1687`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '1688' THEN 1
            ELSE 0
        END)) AS `1688`
    FROM
        ((((`prosp_consult` `a`
        LEFT JOIN `prosp_tag` `b` ON ((`a`.`prosp_consult_id` = `b`.`prosp_consult_id`)))
        LEFT JOIN `vw_dyn_prosp_contact` `c` ON ((`a`.`prosp_consult_id` = `c`.`prosp_consult_id`)))
        LEFT JOIN `vw_dyn_prosp_tag` `d` ON ((`a`.`prosp_consult_id` = `d`.`prosp_consult_id`)))
        LEFT JOIN `vw_dyn_prosp_attach` `e` ON ((`a`.`prosp_consult_id` = `e`.`prosp_consult_id`)))
    GROUP BY `a`.`prosp_consult_id`
    ORDER BY `a`.`prosp_consult_id`