How to write CASE statement with in a CASE? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

How to write CASE statement with in a CASE?

Author

Topic

sql_server_dba
Posting Yak Master

167 Posts

Posted - 2011-01-09 : 22:47:47

Experts...please help me with this query

i have a table....
customer_id locationId price
100 1 0
100 2 30
100 3 40
100 5 0
100 6 70
100 8 90

i need the output of location for the customer. If the locationId is in 1, 2, 3 and sum of price for those locationId's is greater than the locationId in 5, 6, 8 and sum of price then LOCAL else National.

So for the above table, the output should be 100, National.

Thanks in Advance.

Sachin.Nand

2937 Posts

Posted - 2011-01-10 : 01:18:46

[code]
declare @t table(customer_id int,locationId int ,price int)
insert @t
select 100, 1, 0 union
select 100, 2, 30 union
select 100, 3, 40 union
select 100, 5, 0 union
select 100, 6, 70 union
select 100, 8, 90

select customer_id,
case when(select SUM(price)from @t where locationId in(1,2,3)group by customer_id)>
(select SUM(price)from @t where locationId in(5,6,8)group by customer_id) then 'Local' else 'National' end Type
from @t group by customer_id
[/code]

PBUH

sql_server_dba
Posting Yak Master

167 Posts

Posted - 2011-01-10 : 01:47:27

perfect...it worked like a charm :).....thanks a lotttt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources