use av_trng3;
CREATE  or replace    ALGORITHM = UNDEFINED      DEFINER = `av_trng3`@`localhost`      SQL SECURITY DEFINER VIEW `vw_mkt_comp_open_assist_stat` AS     SELECT          `a`.`idmkt_comp_open_assist_stat` AS `idmkt_comp_open_assist_stat`,         `aa`.`idmkt_comp_open_assist` AS `idmkt_comp_open_assist`,         `aa`.`idmkt_comp_open` AS `idmkt_comp_open`,         `b`.`idmkt_comp_ven` AS `idmkt_comp_ven`,         `c`.`companyname` AS `idmkt_comp_ven_nm`,         `b`.`idmkt_comp_client` AS `idmkt_comp_client`,         `d`.`companyname` AS `idmkt_comp_client_nm`,         `b`.`acc_mgr` AS `acc_mgr`,         `f`.`file_name` AS `acc_mgr_fnm`,         CONCAT(`e`.`firstname`, ' ', `e`.`lastname`) AS `acc_mgr_nm`,         `b`.`req_type` AS `req_type`,         IFNULL(`g`.`mkt_value`, 'No Req Type') AS `req_type_nm`,         `b`.`open_dt` AS `open_dt`,         DATE_FORMAT(`b`.`open_dt`, '%m-%d-%y') AS `open_dt_fmt`,         `b`.`loc_city` AS `loc_city`,         `i`.`state_name` AS `state_nm`,         CONCAT(`i`.`state_name`, ', ', `h`.`city_name`) AS `location_nm`,         `b`.`contact_info` AS `contact_info`,         `b`.`source` AS `source`,         IFNULL(`j`.`mkt_value`, 'No Source') AS `source_nm`,         `b`.`open_details` AS `open_details`,         `b`.`close_dt` AS `close_dt`,         IF(ISNULL(`b`.`close_dt`),             'Open',             DATE_FORMAT(`b`.`close_dt`, '%m-%d-%y')) AS `close_dt_fmt`,         IF(ISNULL(`b`.`close_dt`),             'Open',             'Closed') AS `mkt_comp_open_cur_stat`,         `b`.`open_close_day` AS `open_close_day`,         IFNULL(`b`.`open_close_day`,                 (TO_DAYS(NOW()) - TO_DAYS(`b`.`open_dt`))) AS `open_close_day_fmt`,         `b`.`open_cnt` AS `open_cnt`,         `b`.`open_tol` AS `open_tol`,         CONCAT(`b`.`open_cnt`, ' of ', `b`.`open_tol`) AS `open_cnt_tol`,         `b`.`open_pcnt` AS `open_pcnt`,         `aa`.`assist_id` AS `assist_id`,         `m`.`file_name` AS `assist_fnm`,         IF(ISNULL(`aa`.`assist_id`),             'Admin',             CONCAT(`l`.`firstname`, ' ', `l`.`lastname`)) AS `assist_nm`,         `aa`.`assist_rate` AS `assist_rate`,         `aa`.`summary` AS `summary`,         `aa`.`assist_status` AS `cur_assist_status`,         `n`.`mkt_value` AS `cur_assist_status_nm`,         `aa`.`stat_st_dt` AS `cur_stat_st_dt`,         DATE_FORMAT(`aa`.`stat_st_dt`, '%m-%d-%y') AS `cur_stat_st_dt_fmt`,         `aa`.`stat_e_dt` AS `cur_stat_e_dt`,         DATE_FORMAT(`aa`.`stat_e_dt`, '%m-%d-%y') AS `cur_stat_e_dt_fmt`,         `aa`.`no_days` AS `no_days`,         IFNULL(`aa`.`no_days`,                 (TO_DAYS(NOW()) - TO_DAYS(`aa`.`stat_st_dt`))) AS `no_days_vir`,         `a`.`assist_status` AS `assist_status`,         `v`.`mkt_value` AS `assist_status_nm`,         `a`.`stat_st_dt` AS `stat_st_dt`,         DATE_FORMAT(`a`.`stat_st_dt`, '%m-%d-%y') AS `stat_st_dt_fmt`,         `a`.`stat_e_dt` AS `stat_e_dt`,         DATE_FORMAT(`a`.`stat_e_dt`, '%m-%d-%y') AS `stat_e_dt_fmt`,         IF(ISNULL(`a`.`stat_e_dt`),             'Open',             'Closed') AS `mkt_comp_open_assist_cur_stat`,         `a`.`no_days_stat` AS `no_days_stat`,         IFNULL(`a`.`no_days_stat`,                 (TO_DAYS(NOW()) - TO_DAYS(`a`.`stat_st_dt`))) AS `no_days_stat_vir`,         `a`.`updated` AS `updated`,         DATE_FORMAT(`a`.`updated`, '%m-%d-%y %h:%i %p') AS `updated_fmt`,         `a`.`updater` AS `updater`,         `p`.`file_name` AS `updater_fnm`,         IF(ISNULL(`a`.`updater`),             'Admin',             CONCAT(`o`.`firstname`, ' ', `o`.`lastname`)) AS `updater_nm`,         `a`.`idlook_stat_order` AS `idlook_stat_order`,         `a`.`del_dt` AS `del_dt`,         DATE_FORMAT(`a`.`del_dt`, '%m-%d-%y %h:%i %p') AS `del_dt_fmt`,         IFNULL(DATE_FORMAT(`a`.`del_dt`, '%Y'),                 'Not Deleted') AS `del_dt_yr`,         IFNULL(DATE_FORMAT(`a`.`del_dt`, '%M'),                 'Not Deleted') AS `del_dt_mon`,         `a`.`del_by` AS `del_by`,         `s`.`file_name` AS `del_by_fnm`,         IF(ISNULL(`a`.`del_by`),             'Admin',             CONCAT(`r`.`firstname`, ' ', `r`.`lastname`)) AS `del_by_nm`,         `a`.`del_day` AS `del_day`,         IF((`a`.`archv_dt` IS NOT NULL),             'Never',             (TO_DAYS((DATE_FORMAT(`a`.`del_dt`, '%y-%m-%d') + INTERVAL `a`.`del_day` DAY)) - TO_DAYS(DATE_FORMAT(NOW(), '%y-%m-%d')))) AS `del_day_vir`,         `a`.`archv_dt` AS `archv_dt`,         DATE_FORMAT(`a`.`archv_dt`, '%m-%d-%y %h:%i %p') AS `archv_dt_fmt`,         (CASE             WHEN                 (ISNULL(`a`.`del_dt`)                     AND ISNULL(`a`.`archv_dt`))             THEN                 'Not Archived'             WHEN ISNULL(`a`.`del_dt`) THEN DATE_FORMAT(`a`.`archv_dt`, '%Y')             WHEN                 ((`a`.`del_dt` IS NOT NULL)                     AND ISNULL(`a`.`archv_dt`))             THEN                 'Deleted'         END) AS `archv_dt_yr`,         (CASE             WHEN                 (ISNULL(`a`.`del_dt`)                     AND ISNULL(`a`.`archv_dt`))             THEN                 'Not Archived'             WHEN ISNULL(`a`.`del_dt`) THEN DATE_FORMAT(`a`.`archv_dt`, '%M')             WHEN                 ((`a`.`del_dt` IS NOT NULL)                     AND ISNULL(`a`.`archv_dt`))             THEN                 'Deleted'         END) AS `archv_dt_mon`,         `a`.`archv_by` AS `archv_by`,         `u`.`file_name` AS `archv_by_fnm`,         IF(ISNULL(`a`.`archv_by`),             'Admin',             CONCAT(`t`.`firstname`, ' ', `t`.`lastname`)) AS `archv_by_nm`     FROM         (((((((((((((((((((((`mkt_comp_open_assist_stat` `a`         LEFT JOIN `mkt_comp_open_assist` `aa` ON ((`a`.`idmkt_comp_open_assist` = `aa`.`idmkt_comp_open_assist`)))         LEFT JOIN `mkt_comp_open` `b` ON ((`aa`.`idmkt_comp_open` = `b`.`idmkt_comp_open`)))         LEFT JOIN `mkt_comp` `c` ON ((`b`.`idmkt_comp_ven` = `c`.`idmkt_comp`)))         LEFT JOIN `mkt_comp` `d` ON ((`b`.`idmkt_comp_client` = `d`.`idmkt_comp`)))         LEFT JOIN `emp` `e` ON ((`b`.`acc_mgr` = `e`.`emp_id`)))         LEFT JOIN `storages` `f` ON ((`e`.`idstorages` = `f`.`idstorages`)))         LEFT JOIN `mkt_lk_val` `g` ON ((`b`.`req_type` = `g`.`idmkt_lk_val`)))         LEFT JOIN `look_city` `h` ON ((`b`.`loc_city` = `h`.`city_id`)))         LEFT JOIN `look_state` `i` ON ((`h`.`state_id` = `i`.`state_id`)))         LEFT JOIN `mkt_lk_val` `j` ON ((`b`.`source` = `j`.`idmkt_lk_val`)))         LEFT JOIN `emp` `l` ON ((`aa`.`assist_id` = `l`.`emp_id`)))         LEFT JOIN `storages` `m` ON ((`l`.`idstorages` = `m`.`idstorages`)))         LEFT JOIN `mkt_lk_val` `n` ON ((`aa`.`assist_status` = `n`.`idmkt_lk_val`)))         LEFT JOIN `emp` `o` ON ((`a`.`updater` = `o`.`emp_id`)))         LEFT JOIN `storages` `p` ON ((`o`.`idstorages` = `p`.`idstorages`)))         LEFT JOIN `mkt_lk_val` `q` ON ((`a`.`idlook_stat_order` = `q`.`idmkt_lk_val`)))         LEFT JOIN `emp` `r` ON ((`a`.`del_by` = `r`.`emp_id`)))         LEFT JOIN `storages` `s` ON ((`r`.`idstorages` = `s`.`idstorages`)))         LEFT JOIN `emp` `t` ON ((`a`.`archv_by` = `t`.`emp_id`)))         LEFT JOIN `storages` `u` ON ((`t`.`idstorages` = `u`.`idstorages`)))         LEFT JOIN `mkt_lk_val` `v` ON ((`a`.`assist_status` = `v`.`idmkt_lk_val`)))