Blame | Last modification | View Log | RSS feed
## View: v_coach_hours_pivot## +------------+------------------+------+-----+---------+-------+# | Field | Type | Null | Key | Default | Extra |# +------------+------------------+------+-----+---------+-------+# | RCid | varchar(45) | YES | | NULL | |# | derby_name | varchar(45) | YES | | NULL | |# | full_name | varchar(45) | YES | | NULL | |# | id | int(10) unsigned | NO | | 0 | |# | year | int(4) | YES | | NULL | |# | Coach | double | YES | | NULL | |# | Seminar | double | YES | | NULL | |# | Host | double | YES | | NULL | |# | TA | double | YES | | NULL | |# | Other | double | YES | | NULL | |# +------------+------------------+------+-----+---------+-------+CREATE OR REPLACE VIEW `v_coach_hours_pivot` AS select `v_shift`.`RCid` AS `RCid`,`v_shift`.`derby_name` AS `derby_name`,`v_shift`.`full_name` AS `full_name`,`v_shift`.`id` AS `id`,year(`v_shift`.`date`) AS `year`,(case when (`v_shift`.`role` = 'Coach') then (if((`v_shift`.`doubletime` = 1),(`v_shift`.`volhours` / 2),`v_shift`.`volhours`) + ifnull(`v_shift`.`mod_time`,0)) end) AS `Coach`,(case when (`v_shift`.`role` = 'Seminar Coach') then (if((`v_shift`.`doubletime` = 1),(`v_shift`.`volhours` / 2),`v_shift`.`volhours`) + ifnull(`v_shift`.`mod_time`,0)) end) AS `Seminar`,(case when (`v_shift`.`role` = 'Hosted Session Coach') then (if((`v_shift`.`doubletime` = 1),(`v_shift`.`volhours` / 2),`v_shift`.`volhours`) + ifnull(`v_shift`.`mod_time`,0)) end) AS `Host`,(case when (`v_shift`.`role` = 'Assistant (TA)') then (if((`v_shift`.`doubletime` = 1),(`v_shift`.`volhours` / 2),`v_shift`.`volhours`) + ifnull(`v_shift`.`mod_time`,0)) end) AS `TA`,(case when ((`v_shift`.`role` <> 'Hosted Session Coach') and (`v_shift`.`role` <> 'Coach') and (`v_shift`.`role` <> 'Seminar Coach') and (`v_shift`.`role` <> 'Assistant (TA)')) then (if((`v_shift`.`doubletime` = 1),(`v_shift`.`volhours` / 2),`v_shift`.`volhours`) + ifnull(`v_shift`.`mod_time`,0)) end) AS `Other` from `v_shift` where ((`v_shift`.`dept` = 'COA') and (isnull(`v_shift`.`RCid`) = 0) and (`v_shift`.`RCid` <> ''))