Subversion Repositories VORC

## View: v_coach_pay_pivot

#

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

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

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

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

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

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

# | 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    |       |

# | pay        | double      | YES  |     | NULL    |       |

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

CREATE OR REPLACE VIEW `v_coach_pay_pivot` AS select `v_coach_hours_pivot`.`RCid` AS `RCid`,`v_coach_hours_pivot`.`derby_name` AS `derby_name`,`v_coach_hours_pivot`.`full_name` AS `full_name`,`v_coach_hours_pivot`.`year` AS `year`,sum(`v_coach_hours_pivot`.`Coach`) AS `Coach`,sum(`v_coach_hours_pivot`.`Seminar`) AS `Seminar`,sum(`v_coach_hours_pivot`.`Host`) AS `Host`,sum(`v_coach_hours_pivot`.`TA`) AS `TA`,sum(`v_coach_hours_pivot`.`Other`) AS `Other`,(((sum(ifnull(`v_coach_hours_pivot`.`Coach`,0)) * ifnull((select `setting`.`value` from `setting` where (`setting`.`key` = 'COACH_MVP_PAY')),0)) + (sum(ifnull(`v_coach_hours_pivot`.`Host`,0)) * ifnull((select `setting`.`value` from `setting` where (`setting`.`key` = 'COACH_HOST_PAY')),0))) + (sum(ifnull(`v_coach_hours_pivot`.`Seminar`,0)) * ifnull((select `setting`.`value` from `setting` where (`setting`.`key` = 'COACH_SEMINAR_PAY')),0))) AS `pay` from `v_coach_hours_pivot` group by `v_coach_hours_pivot`.`year`,`v_coach_hours_pivot`.`RCid`