MySQL Forums
Forum List  »  MySQL Query Browser

HELP WITH COMPLEX QUERY-URGENT
Posted by: pop cop
Date: April 27, 2011 04:16PM

HI Gurus,
A very weird issue and I cant figure this one out. Please see the below query.Its broken into 4 parts,part 1 is gets all the reported working hours, part 2 gets all the submitted working hours, part 3 gets all the approved working hours, part 4 gets all the manager approved hours. I did a left join since I want all the values for every employee even if they are zeros. I tried putting the whole thing together to be filtered by worked_date. I am not getting the correct results and records are being duplicated. PLEASE HELP!

Select B.WORKED_DATE,Q1.COLLABORATOR_ID, Q1.Name, Q1.RPT_HR, IFNULL(Q2.SUBMITTED_HR,0) AS SUBMITTED_HR, IFNULL(Q3.APP_PM_HR,0) as APP_PM_HR, IFNULL(Q4.APP_MGR_HR,0) as APP_MGR_HR
FROM

(SELECT A.COLLABORATOR_ID,B.WORKED_DATE, concat(A.FIRSTNAME,' ',A.LASTNAME) as Name,
IFNULL(SUM(B.WORKING_HOUR),0) as RPT_HR
FROM tbl_collaborator A LEFT JOIN tbl_time_sheet B
ON (A.COLLABORATOR_ID = B.COLLABORATOR_ID)
GROUP BY A.COLLABORATOR_ID, A.FIRSTNAME, A.LASTNAME,B.WORKED_DATE) AS Q1 LEFT JOIN

(SELECT A.COLLABORATOR_ID,B.WORKED_DATE, concat(A.FIRSTNAME,' ',A.LASTNAME) as Name,
IFNULL(SUM(B.WORKING_HOUR),0) as SUBMITTED_HR
FROM tbl_collaborator A LEFT JOIN tbl_time_sheet B
ON (A.COLLABORATOR_ID = B.COLLABORATOR_ID)
WHERE B.SUBMIT_FLAG = 1
GROUP BY A.COLLABORATOR_ID, A.FIRSTNAME, A.LASTNAME,B.WORKED_DATE) AS Q2
ON (Q1.COLLABORATOR_ID = Q2.COLLABORATOR_ID)
LEFT JOIN

(SELECT A.COLLABORATOR_ID,B.WORKED_DATE, concat(A.FIRSTNAME,' ',A.LASTNAME) as Name,
IFNULL(SUM(B.WORKING_HOUR),0) as APP_PM_HR
FROM tbl_collaborator A LEFT JOIN tbl_time_sheet B
ON (A.COLLABORATOR_ID = B.COLLABORATOR_ID)
WHERE B.APPROVER_ID > 0
GROUP BY A.COLLABORATOR_ID, A.FIRSTNAME, A.LASTNAME,B.WORKED_DATE) AS Q3
ON (Q1.COLLABORATOR_ID = Q3.COLLABORATOR_ID)
LEFT JOIN

(SELECT A.COLLABORATOR_ID,B.WORKED_DATE, concat(A.FIRSTNAME,' ',A.LASTNAME) as Name,
IFNULL(SUM(B.WORKING_HOUR),0) as APP_MGR_HR
FROM tbl_collaborator A LEFT JOIN tbl_time_sheet B
ON (A.COLLABORATOR_ID = B.COLLABORATOR_ID)
WHERE B.APPROVER_MANAGERID > 0
GROUP BY A.COLLABORATOR_ID, A.FIRSTNAME, A.LASTNAME,B.WORKED_DATE) AS Q4
ON (Q1.COLLABORATOR_ID = Q4.COLLABORATOR_ID)
WHERE Q1.WORKED_DATE = '2010-11-03'

Options: ReplyQuote


Subject
Written By
Posted
HELP WITH COMPLEX QUERY-URGENT
April 27, 2011 04:16PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.