Help using CASE in SQL - 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 using CASE in SQL

Author

Topic

Sarakumar
Posting Yak Master

108 Posts

Posted - 2009-04-08 : 05:01:47

declare @Customer table
(cid int,
item varchar(50))
insert into @Customer values (1,'mobile')
insert into @Customer values (1,'TV')
insert into @Customer values(1,'Comp')
insert into @Customer values(1,'Laptop')
insert into @Customer values(2,'mobile')
insert into @Customer values(2,'TV')
insert into @Customer values(3,'mobile')
insert into @Customer values(3,'Laptop')

declare @custname table
(cid int,
cname varchar(20)
)
insert into @custname values (1,'custone')
insert into @custname values (2,'custtwo')
insert into @custname values (3,'custthree')

select * from @Customer a
where
exists (select 1 from @Customer c
where a.cid=c.cid
and c.item = 'mobile' )
and exists (select 1 from @Customer c
where a.cid=c.cid
and c.item = 'TV')

hai in the above scenoria, i want to check something like,
case if ('mobile' and 'tv') then 'PrimeCust'
when 'mobile' then 'normalcust' end
but i dont know how to do this..help pls

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-08 : 05:10:55

[code]select cid,
case
sum(case when item in ('mobile' ,'TV') then 1 else 0 end)
when 2 then 'PrimeCust'
when 1 then 'normalcust'
end
from @Customer
group by cid
[/code]

Madhivanan

Failing to plan is Planning to fail

Sarakumar
Posting Yak Master

108 Posts

Posted - 2009-04-08 : 05:22:58

select cid,
case
sum(case when item in ('mobile' ,'TV') then 1 else 0 end)
when 2 then 'PrimeCust'
when 1 then 'normalcust'
end
from @Customer
group by cid

hai, i dont understand what is this

when 2 then 'PrimeCust'
When 1 then 'normalcust'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-08 : 05:27:26

[code]declare @Customer table
(cid int,
item varchar(50))
insert into @Customer values (1,'mobile')
insert into @Customer values (1,'TV')
insert into @Customer values(1,'Comp')
insert into @Customer values(1,'Laptop')
insert into @Customer values(2,'mobile')
insert into @Customer values(2,'TV')
insert into @Customer values(3,'mobile')
insert into @Customer values(3,'Laptop')

declare @custname table
(cid int,
cname varchar(20)
)
insert into @custname values (1,'custone')
insert into @custname values (2,'custtwo')
insert into @custname values (3,'custthree')

SELECT cid,
CASE
WHEN COUNT(DISTINCT CASE WHEN Item IN ('Mobile', 'TV') THEN Item ELSE NULL END) = 2 THEN 'Prime'
WHEN MAX(CASE WHEN Item = 'Mobile' THEN 1 ELSE 0 END) = 1 THEN 'Normal'
END AS CustType
FROM @Customer
GROUP BY cid[/code]

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-08 : 06:23:48

quote:
Originally posted by Sarakumar

select cid,
case
sum(case when item in ('mobile' ,'TV') then 1 else 0 end)
when 2 then 'PrimeCust'
when 1 then 'normalcust'
end
from @Customer
group by cid

hai, i dont understand what is this

when 2 then 'PrimeCust'
When 1 then 'normalcust'

When cid has both mobile and TV, it will have the count 2 otherwise 1

Madhivanan

Failing to plan is Planning to fail

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2009-04-08 : 08:16:02

Hi,

try this simple one

declare @Customer table
(cid int,
item varchar(50))
insert into @Customer values (1,'mobile')
insert into @Customer values (1,'TV')
insert into @Customer values(1,'Comp')
insert into @Customer values(1,'Laptop')
insert into @Customer values(2,'mobile')
insert into @Customer values(2,'TV')
insert into @Customer values(3,'mobile')
insert into @Customer values(3,'Laptop')

select cid, case cnt when 2 then 'PrimeCust' else 'NormalCust' end CustType from
(
select cid,count(item) cnt from @customer
where item in ('mobile','tv')
group by cid) a

kunal

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-08 : 08:19:32

quote:
Originally posted by kunal.mehta

Hi,

try this simple one

declare @Customer table
(cid int,
item varchar(50))
insert into @Customer values (1,'mobile')
insert into @Customer values (1,'TV')
insert into @Customer values(1,'Comp')
insert into @Customer values(1,'Laptop')
insert into @Customer values(2,'mobile')
insert into @Customer values(2,'TV')
insert into @Customer values(3,'mobile')
insert into @Customer values(3,'Laptop')

select cid, case cnt when 2 then 'PrimeCust' else 'NormalCust' end CustType from
(
select cid,count(item) cnt from @customer
where item in ('mobile','tv')
group by cid) a

kunal

It wont give correct result for all set of data

declare @Customer table
(cid int,
item varchar(50))
insert into @Customer values (1,'mobile')
insert into @Customer values (1,'TV')
insert into @Customer values(1,'Comp')
insert into @Customer values(1,'Laptop')
insert into @Customer values(2,'TV')
insert into @Customer values(2,'TV')
insert into @Customer values(3,'mobile')
insert into @Customer values(3,'Laptop')

select cid, case cnt when 2 then 'PrimeCust' else 'NormalCust' end CustType from
(
select cid,count(item) cnt from @customer
where item in ('mobile','tv')
group by cid) a

Madhivanan

Failing to plan is Planning to fail

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2009-04-08 : 08:29:27

Hi, can u specify when this fails
kunal

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-08 : 09:33:41

quote:
Originally posted by kunal.mehta

Hi, can u specify when this fails
kunal

Run the above and see the result

Madhivanan

Failing to plan is Planning to fail

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2009-04-08 : 23:28:56

Hi,
Yes, you r right. I have updated the query to take care of this

declare @Customer table
(cid int,
item varchar(50))
insert into @Customer values (1,'mobile')
insert into @Customer values (1,'TV')
insert into @Customer values(1,'Comp')
insert into @Customer values(1,'Laptop')
insert into @Customer values(2,'TV')
insert into @Customer values(2,'TV')
insert into @Customer values(3,'mobile')
insert into @Customer values(3,'Laptop')

select cid, case count(cnt) when 2 then 'PrimeCust' else 'NormalCust' end CustType from
(
select distinct cid,item cnt from @customer
where item in ('mobile','tv')
) a
group by a.cid

Kunal

Kokkula
Starting Member

41 Posts

Posted - 2009-04-13 : 05:58:45

Hello,

Try this, using joins

declare @Customer table
(cid int,
item varchar(50))
insert into @Customer values (1,'mobile')
insert into @Customer values (1,'TV')
insert into @Customer values(1,'Comp')
insert into @Customer values(1,'Laptop')
insert into @Customer values(2,'mobile')
insert into @Customer values(2,'TV')
insert into @Customer values(3,'mobile')
insert into @Customer values(3,'Laptop')

declare @custname table
(cid int,
cname varchar(20)
)
insert into @custname values (1,'custone')
insert into @custname values (2,'custtwo')
insert into @custname values (3,'custthree')

SELECT DISTINCT A.cid
,CASE WHEN B.item = 'Mobile' AND C.item = 'TV' THEN 'Prime Cust'
WHEN B.item = 'Mobile' THEN 'Normal Cust'END
FROM @Customer A
LEFT JOIN @Customer B
ON A.cid = B.cid
AND B.item = 'Mobile'
LEFT JOIN @Customer C
On A.cid = C.cid
AND C.item = 'TV'

Hope helpful...

Thanks,
Pavan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-13 : 06:18:39

quote:
Originally posted by Kokkula

Hello,

Try this, using joins

declare @Customer table
(cid int,
item varchar(50))
insert into @Customer values (1,'mobile')
insert into @Customer values (1,'TV')
insert into @Customer values(1,'Comp')
insert into @Customer values(1,'Laptop')
insert into @Customer values(2,'mobile')
insert into @Customer values(2,'TV')
insert into @Customer values(3,'mobile')
insert into @Customer values(3,'Laptop')

declare @custname table
(cid int,
cname varchar(20)
)
insert into @custname values (1,'custone')
insert into @custname values (2,'custtwo')
insert into @custname values (3,'custthree')

SELECT DISTINCT A.cid
,CASE WHEN B.item = 'Mobile' AND C.item = 'TV' THEN 'Prime Cust'
WHEN B.item = 'Mobile' THEN 'Normal Cust'END
FROM @Customer A
LEFT JOIN @Customer B
ON A.cid = B.cid
AND B.item = 'Mobile'
LEFT JOIN @Customer C
On A.cid = C.cid
AND C.item = 'TV'

Hope helpful...

Thanks,
Pavan

With this approach, you should use as many joins as there are values compared

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources