Subversion Repositories VORC

## View: v_class_coaches_current
#
# +-------------+-------------+------+-----+---------+-------+
# | Field       | Type        | Null | Key | Default | Extra |
# +-------------+-------------+------+-----+---------+-------+
# | RCid        | varchar(45) | YES  |     | NULL    |       |
# | derby_name  | varchar(45) | YES  |     | NULL    |       |
# | class_count | varchar(29) | YES  |     | NULL    |       |
# | classes     | mediumtext  | YES  |     | NULL    |       |
# +-------------+-------------+------+-----+---------+-------+

CREATE OR REPLACE VIEW `v_class_coaches_current` AS select distinct `v_shift`.`RCid` AS `RCid`,`v_shift`.`derby_name` AS `derby_name`,concat_ws(' ',cast(count(0) as char charset utf8mb4),if(count(0) > 1,'Classes','Class')) AS `class_count`,group_concat(distinct `v_shift`.`note` separator ' | ') AS `classes` from `v_shift` where `v_shift`.`dept` = 'COA' and `v_shift`.`role` = 'Coach' and `v_shift`.`derby_name` <> '' and year(`v_shift`.`date`) = year(current_timestamp()) group by `v_shift`.`RCid`