Форум пользователей MySQL

SELECT d.doID, d.sokr, (

SELECT count( * )
FROM counter_visit cv
WHERE cv.doID = mt.doID
AND `date`
BETWEEN '2009-05-01'
AND '2009-06-05'
) AS count_v, sum(
CASE WHEN mt.meroprID = '1'
AND fh1.fhaseID = '1'
AND fh1.`date_open`
BETWEEN '2009-05-01'
AND '2009-06-05'
THEN 1
END ) AS count_so, call2.count_call_so, sum(
CASE WHEN mt.meroprID = '1'
AND fh1.statusID = '13'
AND fh1.`date_close`
BETWEEN '2009-05-01'
AND '2009-06-05'
THEN 1
END ) AS count_z_so, sum(
CASE WHEN mt.meroprID = '1'
AND fh1.statusID = '17'
AND fh1.`date_close`
BETWEEN '2009-05-01'
AND '2009-06-05'
THEN 1
END ) AS count_p_so, sum(
CASE WHEN mt.meroprID = '2'
AND fh1.fhaseID = '1'
AND fh1.`date_open`
BETWEEN '2009-05-01'
AND '2009-06-05'
THEN 1
END ) AS count_cr, call2.count_call_cr, sum(
CASE WHEN mt.meroprID = '2'
AND fh1.statusID = '13'
AND fh1.`date_close`
BETWEEN '2009-05-01'
AND '2009-06-05'
THEN 1
END ) AS count_z_cr, sum(
CASE WHEN mt.meroprID = '2'
AND fh1.statusID = '17'
AND fh1.`date_close`
BETWEEN '2009-05-01'
AND '2009-06-05'
THEN 1
END ) AS count_p_cr, sum(
CASE WHEN mt.meroprID = '3'
AND fh1.fhaseID = '1'
AND fh1.`date_open`
BETWEEN '2009-05-01'
AND '2009-06-05'
THEN 1
END ) AS count_rek, call2.count_call_rek, sum(
CASE WHEN mt.meroprID = '3'
AND fh1.statusID = '13'
AND fh1.`date_close`
BETWEEN '2009-05-01'
AND '2009-06-05'
THEN 1
END ) AS count_z_rek, sum(
CASE WHEN mt.meroprID = '3'
AND fh1.statusID = '17'
AND fh1.`date_close`
BETWEEN '2009-05-01'
AND '2009-06-05'
THEN 1
END ) AS count_p_rek, sum(
CASE WHEN mt.meroprID = '4'
AND fh1.fhaseID = '1'
AND fh1.`date_open`
BETWEEN '2009-05-01'
AND '2009-06-05'
THEN 1
END ) AS count_ck, call2.count_call_ck, sum(
CASE WHEN mt.meroprID = '4'
AND fh1.statusID = '13'
AND fh1.`date_close`
BETWEEN '2009-05-01'
AND '2009-06-05'
THEN 1
END ) AS count_z_ck, sum(
CASE WHEN mt.meroprID = '4'
AND fh1.statusID = '17'
AND fh1.`date_close`
BETWEEN '2009-05-01'
AND '2009-06-05'
THEN 1
END ) AS count_p_ck, sum(
CASE WHEN mt.meroprID = '5'
AND fh1.fhaseID = '1'
AND fh1.`date_open`
BETWEEN '2009-05-01'
AND '2009-06-05'
THEN 1
END ) AS count_kos, call2.count_call_kos, sum(
CASE WHEN mt.meroprID = '5'
AND fh1.statusID = '13'
AND fh1.`date_close`
BETWEEN '2009-05-01'
AND '2009-06-05'
THEN 1
END ) AS count_z_kos, sum(
CASE WHEN mt.meroprID = '5'
AND fh1.statusID = '17'
AND fh1.`date_close`
BETWEEN '2009-05-01'
AND '2009-06-05'
THEN 1
END ) AS count_p_kos, sum(
CASE WHEN mt.meroprID = '6'
AND fh1.fhaseID = '1'
AND fh1.`date_open`
BETWEEN '2009-05-01'
AND '2009-06-05'
THEN 1
END ) AS count_sp, call2.count_call_sp, sum(
CASE WHEN mt.meroprID = '6'
AND fh1.statusID = '13'
AND fh1.`date_close`
BETWEEN '2009-05-01'
AND '2009-06-05'
THEN 1
END ) AS count_z_sp, sum(
CASE WHEN mt.meroprID = '6'
AND fh1.statusID = '17'
AND fh1.`date_close`
BETWEEN '2009-05-01'
AND '2009-06-05'
THEN 1
END ) AS count_p_sp, sum(
CASE WHEN IF( (
fh2.filialID = fh1.filialID
AND fh2.doID = fh1.doID
AND fh2.otdelID = fh1.otdelID
), 0, 1 )
AND fh1.`date_open`
BETWEEN '2009-05-01'
AND '2009-06-05'
THEN 1
END ) AS pas
FROM fhase_history fh1
LEFT JOIN map_target mt ON fh1.map_targetID = mt.map_targetID
LEFT JOIN DO d ON fh1.doID = d.doID
LEFT JOIN (

SELECT fh11.filialID, fh11.doID, fh11.otdelID, SUM(
CASE WHEN map_target.meroprID =1
THEN 1
END ) AS count_call_so, SUM(
CASE WHEN map_target.meroprID =2
THEN 1
END ) AS count_call_cr, SUM(
CASE WHEN map_target.meroprID =3
THEN 1
END ) AS count_call_rek, SUM(
CASE WHEN map_target.meroprID =4
THEN 1
END ) AS count_call_ck, SUM(
CASE WHEN map_target.meroprID =5
THEN 1
END ) AS count_call_kos, SUM(
CASE WHEN map_target.meroprID =6
THEN 1
END ) AS count_call_sp
FROM fhase_history fh11
INNER JOIN (

SELECT map_targetID, max( fhase_historyID ) AS max_d
FROM fhase_history
WHERE fhaseID = '2'
AND `date_open`
BETWEEN '2009-05-01'
AND '2009-06-05'
GROUP BY map_targetID
)fh22 ON fh22.max_d = fh11.fhase_historyID
LEFT JOIN map_target ON map_target.map_targetID = fh11.map_targetID
WHERE 1
AND fh11.filialID = '1'
AND fh11.otdelID = '1'
GROUP BY fh11.doID
)call2 ON call2.doID = d.doID
LEFT JOIN (

SELECT *
FROM fhase_history fh11
INNER JOIN (

SELECT map_targetID AS mtID, max( fhase_historyID ) AS max
FROM fhase_history
WHERE 1
AND `date_open`
BETWEEN '2009-05-01'
AND '2009-06-05'
GROUP BY map_targetID
)fh22 ON fh22.mtID = fh11.map_targetID
AND fh22.max = fh11.fhase_historyID
)fh2 ON fh2.map_targetID = fh1.map_targetID
WHERE d.doID IS NOT NULL
AND fh1.filialID = '1'
AND fh1.otdelID = '1'
GROUP BY fh1.doID;