hdcowboyaz
asked on
SQL Syntax
This query
SELECT j.JOB_ID, s.SCTYPE, SUM(c.POPU)/(SUM(c.POPU)/S UM(c.POPU/ c.POUEST)) AS EstHours
FROM ccode c
INNER JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
INNER JOIN job j ON c.JOB_ID = j.JOB_ID
WHERE j.JOB_ID = 7398
AND s.SCTYPE IN (1,2,3,4,5,6,7)
AND c.POPU > 0
AND (j.DELETED != 'Y'OR j.DELETED IS NULL)
AND (c.DELETED != 'Y'OR c.DELETED IS NULL)
AND (s.DELETED != 'Y'OR s.DELETED IS NULL)
GROUP BY j.JOB_ID, s.SCTYPE;
returns
JOB_ID SCTYPE EstHours
7398 1 3280.53
7398 3 1893.76
This query
SELECT a.POEST
FROM (SELECT MAX(c.POPU) POPU, c.POEST
FROM ccode c
JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
JOIN job j ON j.JOB_ID = c.JOB_ID
WHERE j.JOB_ID = 7398
AND s.SCTYPE IN (1,2,3,4,5,6,7)
AND c.POPU > 0
GROUP BY j.JOB_ID, s.SCTYPE)a;
returns
POEST
24.64
25.09
I would like to join/rewite this to ouput (Total = EstHours * POEST)
JOB_ID SCTYPE EstHours POEST TOTAL
7398 1 3280.53 24.64 80832.26
7398 3 1893.76 25.09 47514.44
ERD.jpg
SELECT j.JOB_ID, s.SCTYPE, SUM(c.POPU)/(SUM(c.POPU)/S
FROM ccode c
INNER JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
INNER JOIN job j ON c.JOB_ID = j.JOB_ID
WHERE j.JOB_ID = 7398
AND s.SCTYPE IN (1,2,3,4,5,6,7)
AND c.POPU > 0
AND (j.DELETED != 'Y'OR j.DELETED IS NULL)
AND (c.DELETED != 'Y'OR c.DELETED IS NULL)
AND (s.DELETED != 'Y'OR s.DELETED IS NULL)
GROUP BY j.JOB_ID, s.SCTYPE;
returns
JOB_ID SCTYPE EstHours
7398 1 3280.53
7398 3 1893.76
This query
SELECT a.POEST
FROM (SELECT MAX(c.POPU) POPU, c.POEST
FROM ccode c
JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
JOIN job j ON j.JOB_ID = c.JOB_ID
WHERE j.JOB_ID = 7398
AND s.SCTYPE IN (1,2,3,4,5,6,7)
AND c.POPU > 0
GROUP BY j.JOB_ID, s.SCTYPE)a;
returns
POEST
24.64
25.09
I would like to join/rewite this to ouput (Total = EstHours * POEST)
JOB_ID SCTYPE EstHours POEST TOTAL
7398 1 3280.53 24.64 80832.26
7398 3 1893.76 25.09 47514.44
ERD.jpg
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER