How do I get rid of these subqueries?

I have this request:

select distinct pdi.height, pdi.width, pj.jobnum
        , (select count(barcode) from productiondoorinformation 
            inner join view_productionjobs on view_productionjobs.ctr=productiondoorinformation.productionjobsctr
                where view_productionjobs.jobnum=pj.jobnum 
                    and productiondoorinformation.height=pdi.height 
                    and productiondoorinformation.width=pdi.width
                    and productiondoorinformation.alternaterating='PARTICLE') as particleqty
        , (select count(barcode) from productiondoorinformation 
            inner join view_productionjobs on view_productionjobs.ctr=productiondoorinformation.productionjobsctr
                where view_productionjobs.jobnum=pj.jobnum 
                    and productiondoorinformation.height=pdi.height 
                    and productiondoorinformation.width=pdi.width
                    and productiondoorinformation.alternaterating<>'PARTICLE') as laminatedqty

    from productiondoorinformation pdi inner join view_productionjobs pj on pj.ctr=pdi.productionjobsctr
                where pj.jobnum='' + @Jobnum + ''

      

There must be a better way to be the best way to do it. I hate subheadings, but I'm not sure enough to rewrite it without them. There are two different counts that count, which you will notice when you see the where clauses for each.

This request takes nine seconds. This is too long. Any other query I write against these tables returns immediately. Any suggestions? I've tried a group, etc., but can get it to work either way (particle or particle), but not both.

The returned data should look like this:

height | width | jobnum | particleqty | laminatedqty 
79      49       t1000    10           5
78      49       t1000    1            3
79      47       t1000    15           0

      

+3


source to share


2 answers


You are on the right track. You should use SUM

instead of COUNT

and GROUP BY

:

select pdi.height, pdi.width, pj.jobnum,
        SUM(CASE pdi.alternaterating WHEN 'PARTICLE' THEN 1 ELSE 0 END) particleqty,
        SUM(CASE pdi.alternaterating WHEN 'PARTICLE' THEN 0 ELSE 1 END) laminatedqty
from productiondoorinformation pdi 
     inner join view_productionjobs pj on pj.ctr=pdi.productionjobsctr
     where pj.jobnum='' + @Jobnum + ''
group by pdi.height, pdi.width, pj.jobnum 

      



BTW, not sure if you want those single ( ''

) quotes around @Jobnum ...

+6


source


Just a quick dump - why not write the inner query as a simple table with

CASE WHEN alternaterating = 'PARTICLE' THEN 1 ELSE 0

      



then query this table?

+2


source







All Articles