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 2008 Forums
 Transact-SQL (2008)
 Case when logic has too many or's

Author  Topic 

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2014-08-26 : 12:48:34
I want to write a case when logic with sum aggregation for Month_value.



these are the conditions i recv for DEPT_VISITS:
i tried to put this way within case when:

SUM(CASE WHEN Account =(88000400)
OR (PROG = 67900 and Account =88000000)
OR (PROD = 67900 and ACCOUNT=88000200)
OR (PROG=67900 and ACCOUNT=88000500)
THEN mth_value)
END) DEPT_VISITS,


Is the below logic better incorporating above conditions all:, please advise.

SUM(CASE WHEN Sub_Account =(88000400) then mth_value
when PROG = 67900 and Account =88000000 then mth_value
when PROG = 67900 and ACCOUNT=88000200 then mth value
when PROG=67900 and ACCOUNT=88000500 then mth_value
END)
DEPT_VISITS,




Thanks a lot for the helpful info.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-26 : 12:51:12
Don't you need an ELSE?

SUM(CASE WHEN (Account = 88000400) OR (PROG = 67900 AND Account IN (88000000,88000200,88000500))
THEN mth_value
END) DEPT_VISITS,

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2014-08-26 : 12:59:35
Thank you Tara, I don't have else.
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-08-26 : 13:55:42
You don't need an ELSE. SQL will default it to NULL.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-26 : 14:13:38
I didn't mean a mandatory ELSE, just a business rule/logic reason.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -