Select Case - 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

General SQL Server Forums

New to SQL Server Programming

Select Case

Author

Topic

satheesh
Posting Yak Master

152 Posts

Posted - 2012-06-28 : 11:05:08

Dear All,

I have a table called ‘commission' with the field ‘level’,’amount. And I need to calculate commission amount based on below criteria.

-> Level2 commission if >0, then level2 commission else level1 commission amount

I have tried the below,but when I try to merge i am getting error. How to merge these two case.Any help will be highly appreciated.

case when Commission.level = '2' and Commission.Amount >’0’ then Commission.Amount end as Commision1
case when Commission .level = '2' and Commission.Amount < '0' then Commission.Amount end as Commision2

Sample Data

Id amount level
656 0.190000 1
656 2.010000 2
669 -5.13 2
669 1.45 1

Output
Id Amount
656 2.010
669 1.45

Thanks
Regards,
SG

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-28 : 11:25:04

Not sure what you want to do with the two rows - this is a guess that you sum the amounts and if the result is > 0 take the level2 row, if <0 take the level1
If 0 you will get null. This assumes there are only 2 rows - one for level1 and one for level2

select id, case when amount > 0 then level2 when amount < 0 then level end
from
(select Id, amount = sum(amount), level1 = max(case when level = 1 then Amount else 0 end), level2 = max(case when level = 2 then Amount else 0 end) from tbl group by ID) a

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-28 : 15:58:34

looks like this


SELECT Id,Amount
FROM 
(SELECT ROW_NUMBER() OVER (PARTITION BY Id ORDER BY CASE WHEN amount > 0 then 1 else 0 end desc,level desc) AS Rn,*
FROM table
)t
WHERE Rn=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources