SELECT
P.DC_CODE,
P.DC_NAME,
A.park_code,
P.park_name,
A.ALC_DATE,
A.ALC_TOTAL_NUM,
A.SIGN_CAR_NUM,
A.CAR_WAITING_OVERTIME_NUM,
A.CAR_WORKING_OVERTIME_NUM,
A.ALC_AVG_DURATION,
CONCAT(TO_CHAR(ROUND(100*A.CAR_WAITING_OVERTIME_NUM/A.ALC_TOTAL_NUM,2),'fm9999999990.00'),'%') WAITING_OVERTIME_PERCENT,
CONCAT(TO_CHAR(ROUND(100*A.CAR_WORKING_OVERTIME_NUM/A.ALC_TOTAL_NUM,2),'fm9999999990.00'),'%') WORKING_OVERTIME_PERCENT
FROM
(
SELECT
r.park_code,
r.ALC_DATE,
count( DISTINCT r.TASK_UUID ) ALC_TOTAL_NUM,
count( DISTINCT signTaskUuid ) AS SIGN_CAR_NUM,
count( DISTINCT a.task_uuid ) AS CAR_WAITING_OVERTIME_NUM,
count( DISTINCT b.task_uuid ) AS CAR_WORKING_OVERTIME_NUM,
ROUND( AVG( r.inparkTime ), 2 ) AS ALC_AVG_DURATION
FROM
(
SELECT
park_code,
task_uuid,
ROUND(to_char( DAC_END_TIME-SIGN_START_TIME )* 24 *60) AS inparkTime,
ALC_DATE,
CASE
WHEN SIGN_START_TIME IS NULL THEN
'' ELSE task_uuid
END AS signTaskUuid
FROM
(
SELECT
ALC_BY,
ALC_LUWP_CODE,
DAC_END_TIME,
SIGN_START_TIME,
TASK_UUID,
CASE
WHEN to_char( SIGN_START_TIME, 'hh24mi' ) BETWEEN '1030'
AND '2400' THEN
to_char( SIGN_START_TIME, 'yyyy-mm-dd' )
WHEN to_char( SIGN_START_TIME, 'hh24mi' ) BETWEEN '0000'
AND '1029' THEN
to_char( SIGN_START_TIME - 1, 'yyyy-mm-dd' ) ELSE ''
END AS ALC_DATE,
park_Code
FROM
GQ_RPT_ACT_DETAIL
WHERE
STATUS = 1
<if test="parkCode != null and parkCode != ''" > and PARK_CODE = #{parkCode}</if>
AND SIGN_START_TIME
BETWEEN
to_date(#{alcDateStart},'yy-mm-dd hh24:mi:ss')
AND
to_date(#{alcDateEnd},'yy-mm-dd hh24:mi:ss')
) d
) r
LEFT JOIN gq_info_alarm a ON r.PARK_CODE = a.PARK_CODE
AND r.TASK_UUID = a.TASK_UUID
AND a.at_type = 'LUWP_WAITING_OVERTIME'
LEFT JOIN gq_info_alarm b ON r.PARK_CODE = b.PARK_CODE
AND r.TASK_UUID = b.TASK_UUID
AND b.at_type = 'LUWP_WORKING_OVERTIME'
GROUP BY
r.park_code,
r.ALC_DATE
) A
LEFT JOIN GQ_BASE_PARK P ON A.PARK_CODE = P.PARK_CODE where p.status = 1
<if test="dcCode != null and dcCode != ''" > and DC_CODE = #{dcCode}</if>
超累人的sql(3)
最新推荐文章于 2024-05-21 09:46:31 发布