Specific apply where fiter only to specific column

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)

Specific apply where fiter only to specific column

Author

Topic

newkon12
Starting Member

4 Posts

Posted - 2014-05-26 : 02:40:53

select p.result ,
(case when p.parameterID = 'AVA' then sum(p.Result) else 0 end) as [AVA],
(case parameterId when '2D' then sum(p.Result) else 0 end) [2D],
(case parameterId when 'Sd' then sum(p.Result) else 0 end ) [Sd]
FROM Table1 p
WHERE p.parameterID
in ('AVA', '2D', 'Sd') and p.ResultNo=-1
and ( p.result * 10000 < 1.0 )
group by
p.result, p.parameterID

I in above query in where clause I am using ( p.result * 10000 < 1.0 ) and evaluating it for every value where (p.result * 10000 < 1.0) My requirement is to change this query it should apply only [AVA] for p.result * 10000 < 1.0

I want in where clause ( p.result * 10000 < 1.0 ) should apply/check only column [AVA] it should not apply to all columns.

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-05-26 : 03:16:40

not sure about p.ResultNo should be verify for AVA...


WHERE 
	 p.ResultNo=-1
	 AND

	( (p.parameterID in ('2D', 'Sd'))

	or 

	(p.parameterID = 'AVA' AND ( p.result * 10000 < 1.0 ) ) )

sabinWeb MCP

newkon12
Starting Member

4 Posts

Posted - 2014-05-26 : 09:02:43

I got the solution
replaced (case when p.parameterID = 'AVA' then sum(p.Result) else 0 end) as [AVA], with below line
(case when p.ParameterId='AVA' then sum(CASE when (p.resultvalue * 1) < 1.0 then p.resultvalue else 0 end ) else 0 END) as [AVA]

and removed ( p.result * 10000 < 1.0 ) from where clause.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources