meidu2021/daju/target/classes/cn/com/lzt/attendance/sql/AttendanceStatsMinidaoDao_pojAttendanceStatsData.sql

SELECT   pjtId,yearmonth,pmId,
datumWorkDuration-AdatumDatem+BdatumDate datumWorkDuration,
attendanceDuration-Aattendance_mins+Battendance_mins attendanceDuration,
convert((attendanceDuration-Aattendance_mins+Battendance_mins)/(datumWorkDuration-AdatumDatem+BdatumDate)*100,decimal(10,2)) attendanceRate
FROM
(
		SELECT uat.*,
				(
					SELECT IFNULL(SUM(datum_date),0) from t_bus_user_attendance_detail WHERE  borrowstatus ='1' and borrowspid = uat.pjtId  and DATE_FORMAT(ymd_date,"%Y-%m")=:yearmonth
				) BdatumDate,
				(
					SELECT IFNULL(SUM(actualattendance_date),0) from t_bus_user_attendance_detail WHERE  borrowstatus ='1' and borrowspid = uat.pjtId  and DATE_FORMAT(ymd_date,"%Y-%m")=:yearmonth
				) Battendance_mins 
		FROM
		(
				SELECT   pid pjtId,yearmonth,pmId,
				SUM(datum_attendance_date) datumWorkDuration,
				SUM(actual_attendance_date) attendanceDuration,SUM(AdatumDate) AdatumDatem,SUM(Aattendance_mins) Aattendance_mins
				from
				( 
						SELECT uat.*,
						(
							SELECT IFNULL(SUM(datum_date),0) from t_bus_user_attendance_detail WHERE attendance_id=uat.id and borrowstatus ='1'
						) AdatumDate,
						(
							SELECT IFNULL(SUM(actualattendance_date),0) from t_bus_user_attendance_detail WHERE attendance_id=uat.id and borrowstatus ='1'
						) Aattendance_mins
						FROM(
							SELECT uat.id,uat.user_id,uat.pid,uat.departid,uat.yearmonth,uat.datum_attendance_date,uat.actual_attendance_date,d.dept_proj_managerid pmId
							from t_bus_user_attendance_total uat
							LEFT JOIN t_s_depart d
							ON uat.pid = d.ID
						) uat
						WHERE uat.yearmonth =:yearmonth AND uat.pid != ''
				) a
				GROUP BY pid
		) uat
)re

order by attendanceRate asc