| 123456789101112 |
- SELECT p.pjtId,p.pmId,IFNULL(pus.endmonth_total,p.earlymonthTotal) as earlymonthTotal,p.entryTotal,p.leaveTotal,p.yearmonth,
- IFNULL(IFNULL(pus.endmonth_total,p.earlymonthTotal)+entryTotal-leaveTotal,0) endmonthTotal,
- IFNULL(CONVERT(leaveTotal/IFNULL(pus.endmonth_total,earlymonthTotal)*100,decimal(10,2)),0) flowProportion
- FROM
- (
- SELECT pjt_id pjtId,pm_id pmId,SUM(earlymonth_total) earlymonthTotal, SUM(entry_total) entryTotal, SUM(leave_total) leaveTotal,yearmonth
- from t_bus_dept_user_statistics dus
- WHERE yearmonth =:param.yearmonth
- GROUP BY dus.pjt_id
- ) p
- LEFT JOIN t_bus_proj_user_statistics pus
- ON p.pjtId = pus.pjt_id and pus.yearmonth=:param.beforemotn order by flowProportion desc
|
