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