tough query

[Pattycake245]

I have an interesting problem that I have an answer to but it takes 30 seconds to run. I have a table with records in it something like:

empid queue itemnum
1234 att 0913403
2356 comp 0913430
1234 comp 0913430....

I also have a table with all possible queue's in it, 15 to be exact. I'm trying to get the counts of each queue per empid per itemnum, and also show all queue's even if they have a count of 0. I am doing an inner select for each queue which is why it takes so long to run. I want to do this one line per empid. Something like...

empid compl att queue3 queue4 queue5...
1234 2 1 0 0 0

This is for displaying on a web page. If someone had an idea for a quicker workaround that would be a great help. I can't seem to get the join syntax working the way I want it. Hopefully this makes sense.

thanks in advance, Tim

 

[vongrunt]

Can you show queries that:

- count each qieue per empid per itemnum (standard GROUP BY)
- return all possible queues?

Also: about which web language are we talking, and is it necessary to calculate this crosstab server-side?

 

[Pattycake245]

You know what, I just realized the query that takes the longest is creating a temp table that I don't even reference in the final result set! What a dummy, what can I say, it's Monday. Hopefully I don't run into another snag. This is a Cold Fusion app, and yes I was thinking about doing the crosstab on the Cold Fusion side, but I figured out the query anyway. Just for kicks, here is the query, and it runs in milliseconds:


select a.Project, a.empid,
isnull(sum(case when a.Queue='Special' and a.itemnum=@itemnum then 1 end),0) Special,
isnull(sum(case when a.Queue='Intapp' and a.itemnum=@itemnum then 1 end),0) Intapp,
isnull(sum(case when a.Queue='Intres' and a.itemnum=@itemnum then 1 end),0) Intres,
isnull(sum(case when a.Queue='Busy' and a.itemnum=@itemnum then 1 end),0) Busy,
isnull(sum(case when a.Queue='Retry' and a.itemnum=@itemnum then 1 end),0) Retry,
isnull(sum(case when a.Queue='Att' and a.itemnum=@itemnum then 1 end),0) Att,
isnull(sum(case when a.Queue='Compl' and a.itemnum=@itemnum then 1 end),0) Compl,
isnull(sum(case when a.Queue='Qtserr' and a.itemnum=@itemnum then 1 end),0) Qtserr,
isnull(sum(case when a.Queue='Dead' and a.itemnum=@itemnum then 1 end),0) Dead,
isnull(sum(case when a.Queue='Recoq' and a.itemnum=@itemnum then 1 end),0) Recoq,
isnull(sum(case when a.Queue='Regionov' and a.itemnum=@itemnum then 1 end),0) Regionov,
isnull(sum(case when a.Queue='Extra1' and a.itemnum=@itemnum then 1 end),0) Extra1,
isnull(sum(case when a.Queue='Inactive' and a.itemnum=@itemnum then 1 end),0) Inactive,
isnull(sum(case when a.Queue='Inuse' and a.itemnum=@itemnum then 1 end),0) Inuse,
isnull(sum(case when a.Queue='Indial' and a.itemnum=@itemnum then 1 end),0) Indial
from Firmness_Of_Recruits a full outer join empmaster b on a.empid=b.empid
where a.itemnum=@itemnum
and b.active_indc<>'N'
and b.emply_indc='I'
group by a.itemnum,a.empid
order by a.itemnum,a.empid

Sorry for the nuisance.
Tim