Results 1 to 2 of 2

Thread: Help with complex query-urgent

  1. #1
    Join Date
    Apr 2011
    Posts
    1

    Help with complex query-urgent

    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'

  2. #2
    Join Date
    Feb 2011
    Location
    Melbourne, Australia
    Posts
    13
    Still need help on this one?
    Willing to give hou a hand, but would love to have the table designs, preferably in SQL, to generate a test environment

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •