If or case in a select - 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)

If or case in a select

Author

Topic

lucsky8
Posting Yak Master

105 Posts

Posted - 2008-10-27 : 13:18:32

Hi,

Can someone help?

I have this query:

SELECT t1.strNomObjet,t1.strNomDescripteur,
sum((CAST(strReponse as decimal(15,2))) / strReponsePourcentage * 100) /t2.PersonCount AS ReponseCount 
FROM @Temp as t1
INNER JOIN (SELECT strNomObjet,strNomDescripteur,count(*) AS PersonCount FROM @Temp
			GROUP BY strNomObjet,strNomDescripteur)t2
			ON t2.strNomObjet=t1.strNomObjet
			AND t2.strNomDescripteur=t1.strNomDescripteur
GROUP BY t1.strNomObjet,t1.strNomDescripteur,t2.PersonCount

It working fine the only thing i need to do is :

Instead of just using :
sum((CAST(strReponse as decimal(15,2))) / strReponsePourcentage * 100) /t2.PersonCount AS ReponseCount

I need if strReponse larger then 1000 do something else

In my select if strReponse is larger then 1000 do

sum((CAST(strReponse as decimal(15,2)) - 1000) / strReponsePourcentage * 100) /t2.PersonCount AS ReponseCount
else
sum((CAST(strReponse as decimal(15,2))) / strReponsePourcentage * 100) /t2.PersonCount AS ReponseCount

I hope i explain my self write is there a way to do it with case or??
Tks in advance
Luc

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-27 : 13:22:31

CASE WHEN strReponse > 1000 then .....
else
....
end as ReponseCount

lucsky8
Posting Yak Master

105 Posts

Posted - 2008-10-27 : 13:27:59

quote:
Originally posted by hanbingl

CASE WHEN strReponse > 1000 then .....
else
....
end as ReponseCount

Hi tks for your quick response
I never use case before with sql
Do you mean somthing like this?

It giving me an error "Column '@Temp.strReponse' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
"

Tks for your help

SELECT t1.strNomObjet,t1.strNomDescripteur,
CASE WHEN (strReponse > 1000) then 
	sum((CAST(strReponse as decimal(15,2)) - 1000) / strReponsePourcentage * 100) /t2.PersonCount 
else
	sum((CAST(strReponse as decimal(15,2))) / strReponsePourcentage * 100) /t2.PersonCount 
end
AS ReponseCount 
FROM @Temp as t1
INNER JOIN (SELECT strNomObjet,strNomDescripteur,count(*) AS PersonCount FROM @Temp
			GROUP BY strNomObjet,strNomDescripteur)t2
			ON t2.strNomObjet=t1.strNomObjet
			AND t2.strNomDescripteur=t1.strNomDescripteur
GROUP BY t1.strNomObjet,t1.strNomDescripteur,t2.PersonCount

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 13:33:13

may be this:-

SELECT t1.strNomObjet,t1.strNomDescripteur,
sum(CASE WHEN (strReponse > 1000) then 
	((CAST(strReponse as decimal(15,2)) - 1000) / strReponsePourcentage * 100) /t2.PersonCount 
else
	((CAST(strReponse as decimal(15,2))) / strReponsePourcentage * 100) /t2.PersonCount 
end)
AS ReponseCount 
FROM @Temp as t1
INNER JOIN (SELECT strNomObjet,strNomDescripteur,count(*) AS PersonCount FROM @Temp
			GROUP BY strNomObjet,strNomDescripteur)t2
			ON t2.strNomObjet=t1.strNomObjet
			AND t2.strNomDescripteur=t1.strNomDescripteur
GROUP BY t1.strNomObjet,t1.strNomDescripteur,t2.PersonCount

lucsky8
Posting Yak Master

105 Posts

Posted - 2008-10-27 : 14:49:40

Hi tks it work perfect

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 00:29:12

quote:
Originally posted by lucsky8

Hi tks it work perfect

cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources