I am building a SQL query in Oracle 10g that needs to sum overtime hours for the base table, but needs fields from the parent tables as well. I am looking for the fastest way to build the query. The Project_ID in the first three tables is the same, where the Work_ID is one-to-many to the fourth table.
Table J Project_ID VARCHAR(6) J_Title VARCHAR(25) Table K Project_ID VARCHAR(6) K_Title VARCHAR(25) Table L Project_ID VARCHAR(6) L_Title VARCHAR(25) Work_ID VARCHAR(6) Table C Work_ID VARCHAR(6) L_Title VARCHAR(25) CHRG_DT DATE WRK_HR NUMBER(5,2)
My best guess looks like this (It's simplified, and the date is actually passed as a variable to the stored procedure):
SELECT J.J_Title, K.K_Title, L.L_Title, L.Work_ID, SUM(CASE WHEN TRUNC(C.CHRG_DT) = TO_DATE('09/27/2013', 'mm/dd/yyyy') THEN C.WRK_HR END) AS WRK_HR FROM C, L, K, J WHERE J.Project_ID = 'ABC' AND K.Project_ID = J.Project_ID AND L.Project_ID = K.Project_ID AND C.Work_ID = L.Work_ID GROUP BY L.Work_ID, L.Project_ID, K.Project_ID, J.J_Title, K.K_Title, L.L_Title
UPDATE: Omitted two fields in Table C. Changed Group By fields, and added Work_ID to output for clarity. Sorry for changes, I have to type this from another network, can't just cut and paste. Also trying to leave out unrelated fields.
This is the output I am getting:
J_Title K_Title L_Title Work_ID WRK_HR ------- ------- ------- ------- ------ JOB_ABC KO_ABC LO_ABC DEF 30 JOB_ABC KO_ABC LO_ABC DFG 3
This is the output I am looking for:
J_Title K_Title L_Title WRK_HR ------- ------- ------- ------ JOB_ABC KO_ABC LO_ABC 33