query optimization - 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 2005 Forums

Transact-SQL (2005)

query optimization

Author

Topic

nidabp
Starting Member

15 Posts

Posted - 2011-02-20 : 03:19:10

Hi,
I have 2 tables, a user table and a project table.

User table has all the list of Ids and Names.

The project table has fields - project id, Client Manager,User Manager and Status.

The status can be active, inactive, closed.
The Client Manger and User Manager are the userids from the user table.

For all the users in the user table, I need to get the count of the projects for which the status is active or inactive, for which a user is either a client manager or User Manager. If he is both, the count should be taken as 1.

the tables.
user
userid Name
1 x
2 y
3 z

project table
projectid clientManager UserManager Status
1 x y active
2 x z inactive
3 y y active
4 z x inactive
5 x x active
6 z y inactive

The result table should look like

user active inactive
x 2 2
y 2 1
z 0 3

Thanx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-20 : 03:44:53

SELECT clientManager, SUM(CASE WHEN [Status] = 'Active' THEN 1 ELSE 0 END) AS [Active],
SUM(CASE WHEN [Status] = 'Inactive' THEN 1 ELSE 0 END) AS Inactive
FROM dbo.Table1
GROUP BY clientManager

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources