Subversion Repositories VORC

## View: v_all_hours

#

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

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

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

# | year       | bigint(20)  | YES  |     | NULL    |       |

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

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

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

# | type       | varchar(4)  | NO   |     |         |       |

# | hours      | double      | YES  |     | NULL    |       |

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

CREATE OR REPLACE VIEW `v_all_hours` AS select year(`v_shift_officiating`.`date`) AS `year`,`v_shift_officiating`.`RCid` AS `RCid`,`v_shift_officiating`.`derby_name` AS `derby_name`,'OFF' AS `dept`,'game' AS `type`,sum(`v_shift_officiating`.`volhours`) AS `hours` from `v_shift_officiating` where (isnull(`v_shift_officiating`.`derby_name`) <> 1) group by `year`,`v_shift_officiating`.`RCid` union select year(`v_shift_announcer`.`date`) AS `year`,`v_shift_announcer`.`RCid` AS `RCid`,`v_shift_announcer`.`derby_name` AS `derby_name`,'ANN' AS `dept`,'game' AS `type`,sum(`v_shift_announcer`.`volhours`) AS `hours` from `v_shift_announcer` where (isnull(`v_shift_announcer`.`derby_name`) <> 1) group by `year`,`v_shift_announcer`.`RCid` union select year(`v_shift`.`date`) AS `year`,`v_shift`.`RCid` AS `RCid`,`v_shift`.`derby_name` AS `derby_name`,`v_shift`.`dept` AS `dept`,'hrly' AS `type`,sum(`v_shift`.`volhours`) AS `hours` from `v_shift` where ((isnull(`v_shift`.`derby_name`) <> 1) and (`v_shift`.`dept` <> 'PER') and (`v_shift`.`dept` <> 'CLA')) group by `year`,`v_shift`.`RCid`,`v_shift`.`dept`