best way to develop queries - 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

SQL Server 2000 Forums

Transact-SQL (2000)

best way to develop queries

Author

Topic

bla4free
Starting Member

10 Posts

Posted - 2006-07-13 : 13:20:58

hi. i need to develop some productivity and statistcal reports and i'm having a hard time trying to start. the problem with this is i'm not actually adding up any numbers from columns, i'm having to use the COUNT function to see how many results are returned.

for example

SELECT ProsAtty, count(ProsAtty) as CountOfProsAtty
FROM DefendantCase
WHERE StatusID=3

What my query is doing is asking for a count of attorneys (ProsAtty) who have closed cases (StatusID=3).

This code will give me the following results:

ProsAtty   CountofProsAtty
2          18
4          23
8          19
23         27
etc...

I want to be able to expand this query by including different columsn for the different case statuses, like Open, Pending, Indicted, etc. Is there an easy way to do this without having multiple queries?

Thanks for your help!

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-13 : 13:24:07

select ProsAtty, Status1 = sum(case when StatusID=1 then 1 else 0 end), Status2 = sum(case when StatusID=2 then 1 else 0 end), Status3 = sum(case when StatusID=3 then 1 else 0 end)
from DefendantCase
group by ProsAtty

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

bla4free
Starting Member

10 Posts

Posted - 2006-07-13 : 13:47:24

Thank you so much nr! That's exactly what I was looking for!

Now, is there a way I can throw in additional parameters, especially here:

Status1 = sum(case when StatusID=1 then 1 else 0 end)

I want to be able to limit the data even more to like a particular date. However, the date will come from a different table, called Events.

If I was going to do this in a where clause, it would be something like this:

WHERE Events.EventID=1 and Events.EventDate='1/1/06'

would i add WHERE to my query or would I include this information in HAVING?

Thanks!

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-07-13 : 15:18:24

bla4free

If I understood ur Question correctly,
1st Question : Read more about CASE statement in BOL

eg. Status1 = sum(case when StatusID=1 then 1
case when StatusID=2 then 4
case when StatusID=3 then 9
else 0 end)

2nd Q: U may need to learn more about Where & Having (Having is used in Group By ....)

Srinika

bla4free
Starting Member

10 Posts

Posted - 2006-07-13 : 16:08:50

thank you srinika! books online was very helpful distinguishing the HAVING and WHERE clauses.

Now, I would like to be able to take my query a little farther by putting statistcal information in a new column. I'm looking for something like this:


ProsAtty   ClosedCases  PercentageAtty
2          18               .21
4          23               .26
8          19               .22
23         27               .31

Is there a way i can divide each's attorney's # of closed cases to get a percentage (or a decimal) in a new column? thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources