Query Efficiency

[Leighton21]

Hi All,

Just a question I have the following query and am wondering as to whether it is as efficient as it could be

Select CLI_CODE,
PROJ_CODE,
Coalesce((select avg(EXPECTEDTA)
from PROFJOB_CUIDUSER PJCU, PROFJOB_CUID_SCHEME_ANALYTE PJCSA, PROFJOB_CUID PJC, PROFJOB PJ
where PJC.PRO_JOB = PJ.PRO_JOB
and PJC.PRO_JOB = PJCSA.PRO_JOB
and PJC.CUID = PJCSA.CUID
and PJCU.PRO_JOB = PJC.PRO_JOB
and PJCU.CUID = PJC.CUID
and PJC.VALIDATED >= '20070101 00:00:00'
and PJC.VALIDATED <= '20070125 10:16:10'
and PJCSA.ANALYTESTATUS <> 'LNR'
and PJC.SAMPLETYPE = 'UNK'
and PJ.CLI_CODE = PROFJOB.CLI_CODE
and PJ.PROJ_CODE = PROFJOB.PROJ_CODE),0) AS 'AVERAGE TA',
(select COUNT(DISTINCT PJC.CUID)
from PROFJOB_CUIDUSER PJCU, PROFJOB_CUID_SCHEME_ANALYTE PJCSA, PROFJOB_CUID PJC, PROFJOB PJ
where PJC.PRO_JOB = PJ.PRO_JOB
and PJC.PRO_JOB = PJCSA.PRO_JOB
and PJC.CUID = PJCSA.CUID
and PJCU.PRO_JOB = PJC.PRO_JOB
and PJCU.CUID = PJC.CUID
and PJC.VALIDATED >= '20070101 00:00:00'
and PJC.VALIDATED <= '20070125 10:16:10'
and PJCSA.ANALYTESTATUS <> 'LNR'
and PJC.SAMPLETYPE = 'UNK'
and PJCU.RATIOTA < 0.9
and PJ.CLI_CODE = PROFJOB.CLI_CODE
and PJ.PROJ_CODE = PROFJOB.PROJ_CODE) AS '<0.9',
from JOB
group by CLI_CODE, PROJ_CODE
order by CLI_CODE

It basically has an outer query which selects a client and a project and then selects aggregate information in the subquery using the outer Project and Job Pair.

Can anyone suggest if this is the most effcient way of performing this type of operation??

Any comments will be greatly appreciated

FYI will produce something like

Client Project Avg TA <0.9
GEOLOGY DRILL-CAPEL 15.0 91.1

 

[ESquared]

for starters you should use ansi joins instead of this old syntax.