Query advice - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query advice

Author

Topic

chris_cs
Posting Yak Master

223 Posts

Posted - 2009-03-26 : 10:55:17

I have had to produce a report that summarises the amount of work done by a specific staff member within a specific date range.

I was under a bit of pressure to get this done so I used a query for my report that I got to work. Below is a small part of this:


select 
TotalGeneral = (
select count(wh.caseid)
from workhistory wh
inner join employee e on e.employeeno = wh.employeeno
inner join cases c on c.caseid = wh.caseid
where e.abbreviatedname = @fe
and left(c.irn, 1) = 'G'),
TotalPatents1 = (
select count(wh.caseid)
from workhistory wh
inner join employee e on e.employeeno = wh.employeeno
inner join cases c on c.caseid = wh.caseid
where e.abbreviatedname = @fe
and left(c.irn, 1) = 'P'
and right(c.irn, 2) in ('EP', 'GB', 'WO')),
TotalPatents2 = (
select count(wh.caseid)
from workhistory wh
inner join employee e on e.employeeno = wh.employeeno
inner join cases c on c.caseid = wh.caseid
where e.abbreviatedname = @fe
and left(c.irn, 1) = 'P'
and right(c.irn, 2) not in ('EP', 'GB', 'WO'))

As you can see from the code I'm referencing the same table each time (Workhistory), but I don't think the way I've written the query gives the best performance. I was thinking I should maybe use a CTE but I'm not sure if this would be best.

Are there any suggestions from the code above as to how I could write this better?

I'm happy to try and explain what I'm trying to achieve in more detail if needed.

Thanks in advance

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-26 : 11:16:56

[code]select
TotalGeneral= SUM(case when left(c.irn, 1) = 'G' then 1 else 0 end),
TotalPatents1=SUM(case when left(c.irn, 1) = 'P' and right(c.irn, 2) in ('EP', 'GB', 'WO') then 1 else 0 end),
TotalPatents2=SUM(case when left(c.irn, 1) = 'P' and right(c.irn, 2) not in ('EP', 'GB', 'WO')then 1 else 0 end)
from
workhistory wh
inner join employee e on e.employeeno = wh.employeeno
inner join cases c on c.caseid = wh.caseid
where
e.abbreviatedname = @fe[/code]

chris_cs
Posting Yak Master

223 Posts

Posted - 2009-03-26 : 11:27:21

Thanks for the reply.

That way looks alot more manageable and appears to process alot quicker.

chris_cs
Posting Yak Master

223 Posts

Posted - 2009-03-26 : 11:38:34

If I only wanted to summarise distinct values how would I achieve this with your example?

So I was only displaying the total number of dictinct cases.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-26 : 13:25:26

[code]select
TotalGeneral= count(distinct case when left(c.irn, 1) = 'G' then wh.caseid else null end),
TotalPatents1=count(distinct case when left(c.irn, 1) = 'P' and right(c.irn, 2) in ('EP', 'GB', 'WO') then wh.caseid else null end),
TotalPatents2=count(distinct case when left(c.irn, 1) = 'P' and right(c.irn, 2) not in ('EP', 'GB', 'WO')then wh.caseid else null end)
from
workhistory wh
inner join employee e on e.employeeno = wh.employeeno
inner join cases c on c.caseid = wh.caseid
where
e.abbreviatedname = @fe[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources