help in case function - 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)

help in case function

Author

Topic

abcd
Yak Posting Veteran

92 Posts

Posted - 2009-03-26 : 05:12:45

hi all...
Can anyone tell me some syntax about a case query...
Its like
case when statusid=o
then do nothing
and when statusid<>0
then sum(variable1)
and sum(variable2)
and sum(variable3)
i.e when the requirement is not fulfilled then do same procedure to get three results in one go...

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-03-26 : 05:26:43

Please post the table structure & the desired output.

case when statusid=0
then 0 Else sum(variable1) + sum(variable2)+ sum(variable3)End

abcd
Yak Posting Veteran

92 Posts

Posted - 2009-03-26 : 05:35:59

I am sorry to give sucha a small detail about my problem...
In my query i have used case statement like this..
,Percentverified=CASE WHEN (TStatusUpdate.StatusId =0)
THEN '0'
WHEN ((TStatusUpdate.StatusId) <>0)
THEN ( SUM(CASE TaskStatus.statusname WHEN 'completed' THEN 1 ELSE 0 END) *100/COUNT(TStatusUpdate.StatusId ) )
END
,PercentUnVerified=CASE WHEN (TStatusUpdate.StatusId =0)
THEN '0'
WHEN ((TStatusUpdate.StatusId) <>0)
THEN (SUM(CASE TaskStatus.statusname WHEN 'verified' THEN 1 ELSE 0 END) *100/COUNT(TStatusUpdate.StatusId ))
END
,MaxPercentage=CASE WHEN (TStatusUpdate.StatusId =0)
THEN '0'
WHEN ((TStatusUpdate.StatusId) <>0)
THEN (max(CASE TaskStatus.statusname WHEN 'completed' THEN 1 ELSE 0 END) *100/COUNT(TStatusUpdate.StatusId ))
END

Everything is working fine ,the problem is that for calculatring percentage i have to divide by statusid,but in some places statusid is 0,so i have to arrange case as above...

I want to know is there any other way out to do this...
I think now i am able to clarify my problem.

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-03-26 : 06:01:10

One way would be to use something like this

SUM(CASE when TaskStatus.statusname = 'completed' and COUNT(TStatusUpdate.StatusId )<>0 THEN 1 ELSE 0 END)*100/COUNT(TStatusUpdate.StatusId)

But since you cannot use an aggregate function on another aggregate function it will gv an error.So you can put the count is some varaible & retext the query sth lk this.

SUM(CASE when TaskStatus.statusname = 'completed' and @cnt<>0 THEN 1 ELSE 0 END)*100/@cnt
where @cnt contains the count of your table.

abcd
Yak Posting Veteran

92 Posts

Posted - 2009-03-26 : 06:16:31

sir for some users statusid is 0 when they dont have any task assigned...
in my query i have to calculate three percentages only when the statusid field is 0 in task_statusupdate table.....

i.e.
when statusid<>0
then three cases i told you earlier for that user.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources