SELECT u.survey_user_code AS user_code, (
SELECT count(1)
FROM prpm_loss_item_cyp cyp
WHERE date(finish_case_time) = date
AND claim_status = ?
AND cyp.surveyor_code = u.survey_user_code
) + (
SELECT count(1)
FROM prpm_loss_item m
WHERE date(m.finish_case_time) = date
AND m.loss_status = ?
AND m.surveyor_code = u.survey_user_code
) AS upload_num_day
, (
SELECT COUNT(1)
FROM prpm_loss_item m
WHERE HOUR(timediff(m.finish_case_time, m.create_time)) > ?
AND date(m.finish_case_time) = DATE
AND m.loss_status = ?
AND m.surveyor_code = u.survey_user_code
) AS submit_numTF_day
, (
SELECT COUNT(1)
FROM prpm_loss_item m
WHERE HOUR(timediff(m.finish_case_time, m.create_time)) > ?
AND date(m.finish_case_time) = DATE
AND m.loss_status = ?
AND m.surveyor_code = u.survey_user_code
) AS submit_numST_day, (
SELECT count(1)
FROM prpm_loss_item_cyp cyp
WHERE MONTH(cyp.finish_case_time) = MONTH
AND YEAR(cyp.finish_case_time) = YEAR
AND claim_status = ?
AND cyp.surveyor_code = u.survey_user_code
) + (
SELECT count(1)
FROM prpm_loss_item m
WHERE MONTH(m.finish_case_time) = MONTH
AND YEAR(m.finish_case_time) = YEAR
AND m.loss_status = ?
AND m.surveyor_code = u.survey_user_code
) AS upload_num_month
, (
SELECT COUNT(1)
FROM prpm_loss_item m
WHERE HOUR(timediff(m.finish_case_time, m.create_time)) > ?
AND MONTH(m.finish_case_time) = MONTH
AND YEAR(m.finish_case_time) = YEAR
AND m.loss_status = ?
AND m.surveyor_code = u.survey_user_code
) AS submit_numTF_month
, (
SELECT COUNT(1)
FROM prpm_loss_item m
WHERE HOUR(timediff(m.finish_case_time, m.create_time)) > ?
AND MONTH(m.finish_case_time) = MONTH
AND YEAR(m.finish_case_time) = YEAR
AND m.loss_status = ?
AND m.surveyor_code = u.survey_user_code
) AS submit_numST_month, (
SELECT count(1)
FROM prpm_loss_item_cyp cyp
WHERE YEAR(cyp.finish_case_time) = YEAR
AND claim_status = ?
AND cyp.surveyor_code = u.survey_user_code
) + (
SELECT count(1)
FROM prpm_loss_item m
WHERE YEAR(m.finish_case_time) = YEAR
AND m.loss_status = ?
AND m.surveyor_code = u.survey_user_code
) AS upload_num_year
, (
SELECT COUNT(1)
FROM prpm_loss_item m
WHERE HOUR(timediff(m.finish_case_time, m.create_time)) > ?
AND YEAR(m.finish_case_time) = YEAR
AND m.loss_status = ?
AND m.surveyor_code = u.survey_user_code
) AS submit_numTF_year
, (
SELECT COUNT(1)
FROM prpm_loss_item m
WHERE HOUR(timediff(m.finish_case_time, m.create_time)) > ?
AND YEAR(m.finish_case_time) = YEAR
AND m.loss_status = ?
AND m.surveyor_code = u.survey_user_code
) AS submit_numST_year
FROM prpm_survey_user u