T-SQL Query - 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)

T-SQL Query

Author

Topic

pradeep_iete
Yak Posting Veteran

84 Posts

Posted - 2008-06-09 : 04:31:03

I have the following result set
applicationID statusID
1734 2
2910 2
1734 44
1734 48
2910 46
2910 38
1734 46
2910 20
2910 21
2910 22
Now i want to check for a paricular statusID for all applicationID.That is i want to check that how many application have not attainted that status.
What shud be the query ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-09 : 04:53:34

[code]SELECT applicationID
FROM Table
GROUP BY applicationID
HAVING SUM(CASE WHEN statusID=yourvalue THEN 1 ELSE 0 END) =0[/code]

raky
Aged Yak Warrior

767 Posts

Posted - 2008-06-09 : 05:01:22

declare @app table (applicationID int,statusID int)

insert into @app
select 1734,2 UNION ALL
select 2910, 2 UNION ALL
select 1734 ,44 UNION ALL
select 1734,48 UNION ALL
select 2910,46 UNION ALL
select 2910,38 UNION ALL
select 1734 ,46 UNION ALL
select 2910, 20 UNION ALL
select 2910, 21 UNION ALL
select 2910 , 22

declare @status int
select @status = 44 < for example >

select applicationID
from @app
where statusID <> @status

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-09 : 05:17:02

quote:
Originally posted by raky

declare @app table (applicationID int,statusID int)

insert into @app
select 1734,2 UNION ALL
select 2910, 2 UNION ALL
select 1734 ,44 UNION ALL
select 1734,48 UNION ALL
select 2910,46 UNION ALL
select 2910,38 UNION ALL
select 1734 ,46 UNION ALL
select 2910, 20 UNION ALL
select 2910, 21 UNION ALL
select 2910 , 22

declare @status int
select @status = 44 < for example >

select applicationID, statusid
from @app
where statusID = @status

This will still give application id which still have record with status as 44
1734 ,44
for ex:your query returns
1734,2 which still has Status of 44 in

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources