Subversion Repositories VORC

## View: v_temp_coach_hours

#

# +------------+-------------+------+-----+---------+-------+

# | Field      | Type        | Null | Key | Default | Extra |

# +------------+-------------+------+-----+---------+-------+

# | derby_name | varchar(45) | YES  |     | NULL    |       |

# | full_name  | varchar(45) | YES  |     | NULL    |       |

# | email      | varchar(45) | YES  |     | NULL    |       |

# | MVP        | varchar(23) | YES  |     | NULL    |       |

# | Seminar    | varchar(23) | YES  |     | NULL    |       |

# | Other      | varchar(23) | YES  |     | NULL    |       |

# +------------+-------------+------+-----+---------+-------+

CREATE OR REPLACE VIEW `v_temp_coach_hours` AS select `temp666`.`derby_name` AS `derby_name`,`temp666`.`full_name` AS `full_name`,`temp666`.`email` AS `email`,max((case when (`temp666`.`locale` = 'MVP') then `temp666`.`hours` else '' end)) AS `MVP`,max((case when (`temp666`.`locale` = 'Seminar') then `temp666`.`hours` else '' end)) AS `Seminar`,max((case when (`temp666`.`locale` = 'Other') then `temp666`.`hours` else '' end)) AS `Other` from (select `v_shift`.`derby_name` AS `derby_name`,`v_shift`.`full_name` AS `full_name`,`rollerco_vorc`.`official`.`email` AS `email`,(case when (`v_shift`.`location` like '%MVP%') then 'MVP' when (`v_shift`.`location` like '%Seminar%') then 'Seminar' else 'Other' end) AS `locale`,sum(`v_shift`.`volhours`) AS `hours` from (`rollerco_vorc`.`v_shift` left join `rollerco_vorc`.`official` on((`v_shift`.`RCid` = `rollerco_vorc`.`official`.`RCid`))) where ((`v_shift`.`dept` = 'COA') and (isnull(`v_shift`.`RCid`) = 0) and (year(`v_shift`.`date`) = year(now()))) group by `v_shift`.`RCid`,`locale` order by `v_shift`.`RCid`) `temp666` group by `temp666`.`derby_name`