This query works great but is limited to one job (i.e., JOB_ID = 7398). I wish to open up the query so the first column JOB_ID displays all jobs. However, if I remove WHERE j.job_id = 7398 in query a & b I get a cartesian product.
SELECT a.job_id,
a.sctype,
a.esthours,
b.poest,
(a.esthours * b.poest) AS total
FROM ( SELECT j.job_id, s.sctype, SUM(c.popu) / (SUM(c.popu) / SUM(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) a
INNER JOIN (SELECT x.sctype, x.poest
FROM ( SELECT s.sctype, 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) x) b
ON a.sctype = b.sctype;
GROUP BY j.job_id, s.sctype) a
INNER JOIN (SELECT x.sctype, x.poest
FROM ( SELECT s.sctype, MAX(c.popu) popu, c.poest
To:
GROUP BY j.job_id, s.sctype) a
INNER JOIN (SELECT x.sctype, x.poest, j.job_ID
FROM ( SELECT s.sctype, MAX(c.popu) popu, c.poest, j.job_ID
and modify the last row from:
ON a.sctype = b.sctype;
To:
ON a.sctype = b.sctype AND a.job_ID = b.job_ID.
0
hdcowboyazAuthor Commented:
I used this..
SELECT
a.job_id 'JOB ID',
a.sctype 'TYPE',
a.units 'UNITS',
FORMAT(a.esthours, 2) 'EstHours',
b.poest 'RATE',
FORMAT((a.esthours * b.poest), 2) 'EstCost',
FORMAT((a.esthours * b.poest) / a.units, 4) 'UnitCost'
FROM
(SELECT
j.JOB_ID,
s.SCTYPE,
SUM(c.POPU) AS UNITS,
SUM(c.POPU)/(SUM(c.POPU)/SUM(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 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) a
INNER JOIN (SELECT x.sctype, x.poest, j.job_ID
FROM (SELECT s.sctype, MAX(c.popu) popu, c.poest, j.job_ID
FROM ccode c
JOIN sccode s ON s.sccode_id = c.sccode_id
JOIN job j ON j.job_id = c.job_id
WHERE s.sctype IN (1,2,3,4,5,6,7)
AND c.popu > 0
GROUP BY j.job_id, s.sctype) X) b
ON a.sctype = b.sctype AND a.job_ID = b.job_ID;
I got this error...
Query : SELECT a.job_id 'JOB ID', a.sctype 'TYPE', a.units 'UNITS', format(a.esthours, 2) 'EstHours', b.poest 'RATE', f...
Error Code : 1054
Unknown column 'j.job_ID' in 'field list'
0
hdcowboyazAuthor Commented:
Only one small error
> GROUP BY j.job_id, s.sctype) a
> INNER JOIN (SELECT X.sctype, X.poest, X.job_ID
> FROM ( SELECT s.sctype, MAX(c.popu) popu, c.poest, j.job_ID
There, in the 2nd line, X.job_ID
0
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.