How to use this if statement/Case please

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

How to use this if statement/Case please

Author

Topic

dhani
Posting Yak Master

132 Posts

Posted - 2009-02-20 : 12:27:49

Hello All,

in the below query i am grouping the accounts types

Nettotal is contains all accounts of Netloans,non-equity,suspence,swans, plus new accounts MAXLIFE, NEWLIFE

when i am using below query, i am getting nettotal wrong (it is just addition of MAXLIFE,NEWLIFE accts, not the all accounts)

how can i change my query to count net total as

Nettotal ---> all accounts of Netloans,non-equity,suspence,swans, plus new accounts MAXLIFE, NEWLIFE

select
if(AcctType in ('Borrow','Rest','Netomer','OD')) then ('B.Debt')

else if (AcctTypein ('AC', 'BE', 'CD', 'EK', 'MG)) then ('Non-Equity')

else if (AcctType in ('CAPS','PAPS')) then ('Net Loans')

else if(AcctType in ('Cash Pre','Investments Own')) then ('Invest')

else if (AcctType = 'Suspense Mod') then ('Suspense')

else if (AcctType in ('Swan - Mobility','Swan - Borrow')) then('Swans')

else if (AcctType in ('CAPS','PAPS','AC', 'BE', 'CD', 'EK', 'MG,'Suspense Mod','Swan - Mobility','Swan - Borrow','MAXLIFE','NEWLIFE' )) then ('Net Total')

else (AcctType) as AcctGrpName
, sum(amount)
from accounts group by acctGrpName

Please Help me
Thanks in advance

Best Regards
dhani

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-02-20 : 12:38:35

[code]
SELECT [AcctGrpName] =
CASE
WHEN AcctType in ('Borrow','Rest','Netomer','OD') THEN 'B.Debt'
WHEN AcctType in ('AC', 'BE', 'CD', 'EK', 'MG') THEN 'Non-Equity'
WHEN AcctType in ('CAPS','PAPS') THEN 'Net Loans'
WHEN AcctType in ('Cash Pre','Investments Own') THEN 'Invest'
WHEN AcctType = 'Suspense Mod' THEN 'Suspense'
WHEN AcctType in ('Swan - Mobility','Swan - Borrow') THEN 'Swans'
WHEN AcctType in ('CAPS','PAPS','AC', 'BE', 'CD', 'EK', 'MG','Suspense Mod','Swan - Mobility'
,'Swan - Borrow','MAXLIFE','NEWLIFE' ) THEN 'Net Total'
ELSE AcctType
END
[/code]

Jim

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-20 : 12:42:19

your query doesnt make much sense. probably you meant this


select acctGrpName, sum(amount)
from
(
select 
case
when AcctType in ('Borrow','Rest','Netomer','OD') then 'B.Debt' 

when  AcctTypein ('AC', 'BE', 'CD', 'EK', 'MG) then 'Non-Equity' 

when  AcctType in ('CAPS','PAPS') then 'Net Loans' 

when  AcctType in ('Cash Pre','Investments Own') then 'Invest' 

when  AcctType = 'Suspense Mod' then 'Suspense' 

when  AcctType in ('Swan - Mobility','Swan - Borrow') then 'Swans'

when  AcctType in ('CAPS','PAPS','AC', 'BE', 'CD', 'EK', 'MG,'Suspense Mod','Swan - Mobility','Swan - Borrow','MAXLIFE','NEWLIFE' )) then ('Net Total')

else AcctType 
end as AcctGrpName,amount
from accounts
)t
 group by acctGrpName
with rollup

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources