CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `jitenpatel`@`%` 
    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 '633' THEN 1
            ELSE 0
        END)) AS `633`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '130' THEN 1
            ELSE 0
        END)) AS `130`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '131' THEN 1
            ELSE 0
        END)) AS `131`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '218' THEN 1
            ELSE 0
        END)) AS `218`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '441' THEN 1
            ELSE 0
        END)) AS `441`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '813' THEN 1
            ELSE 0
        END)) AS `813`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '142' THEN 1
            ELSE 0
        END)) AS `142`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '210' THEN 1
            ELSE 0
        END)) AS `210`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '223' THEN 1
            ELSE 0
        END)) AS `223`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '803' THEN 1
            ELSE 0
        END)) AS `803`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '805' THEN 1
            ELSE 0
        END)) AS `805`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '814' THEN 1
            ELSE 0
        END)) AS `814`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '835' THEN 1
            ELSE 0
        END)) AS `835`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '817' THEN 1
            ELSE 0
        END)) AS `817`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '820' THEN 1
            ELSE 0
        END)) AS `820`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '818' THEN 1
            ELSE 0
        END)) AS `818`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '819' THEN 1
            ELSE 0
        END)) AS `819`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '821' THEN 1
            ELSE 0
        END)) AS `821`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '836' THEN 1
            ELSE 0
        END)) AS `836`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '134' THEN 1
            ELSE 0
        END)) AS `134`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '136' THEN 1
            ELSE 0
        END)) AS `136`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '205' THEN 1
            ELSE 0
        END)) AS `205`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '823' THEN 1
            ELSE 0
        END)) AS `823`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '209' THEN 1
            ELSE 0
        END)) AS `209`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '172' THEN 1
            ELSE 0
        END)) AS `172`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '211' THEN 1
            ELSE 0
        END)) AS `211`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '443' THEN 1
            ELSE 0
        END)) AS `443`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '806' THEN 1
            ELSE 0
        END)) AS `806`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '185' THEN 1
            ELSE 0
        END)) AS `185`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '186' THEN 1
            ELSE 0
        END)) AS `186`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '807' THEN 1
            ELSE 0
        END)) AS `807`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '808' THEN 1
            ELSE 0
        END)) AS `808`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '758' THEN 1
            ELSE 0
        END)) AS `758`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '183' THEN 1
            ELSE 0
        END)) AS `183`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '187' THEN 1
            ELSE 0
        END)) AS `187`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '195' THEN 1
            ELSE 0
        END)) AS `195`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '199' THEN 1
            ELSE 0
        END)) AS `199`,
        SUM((CASE `b`.`prosp_tags`
            WHEN '212' THEN 1
            ELSE 0
        END)) AS `212`
    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`