NESTED CASE Statement Question

[mkw77]

Hi, Can anyone help with my query. I have the following case statement. I am trying to set one variable, once I have set the first variable with the value, based on this value I want to set another variable. A kind of nested case I guess but not sure!!
My query is:-
SELECT placeref, mgearea, 'area' =
CASE
WHEN mgearea IN ('Alton','Kingsley')
THEN 'Cheadle'
WHEN mgearea IN('holby','Biddulph Moor')
THEN 'Biddulph'
ELSE 'Other'
END
FROM tempdb..people
ORDER BY placeref
INSERT INTO #tmp_mareas

Need to say if its Cheadle then SubArea = CHTOWN
so I would want my output to look like this:-

placeref mgearea area subarea
CHL0012 Alton Cheadle CHLTOWN
etc...

anyones help is much appreciated.

 

[r937]

you'll need a separate CASE expression for each column

SELECT placeref
     , mgearea
     , CASE WHEN mgearea IN ('Alton','Kingsley') 
            THEN 'Cheadle'   
            WHEN mgearea IN ('holby','Biddulph Moor') 
            THEN 'Biddulph'
            ELSE 'Other'  END   AS [red]Area[/red]
     , CASE WHEN mgearea IN ('Alton','Kingsley') 
            THEN 'Chtown'   
            WHEN mgearea IN ('holby','Biddulph Moor') 
            THEN 'Foobar'
            ELSE 'Other'  END   AS [red]SubArea[/red]
  FROM ...

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[NastiaMurzin]

This is the way I would recommend going about when you have only two variables to compare.

use tableschema;
go
create procedure t
if table tablename is not null
drop table tablename;
go
declare @variablename;
execute tablename.procedurename
@variablename output
if @variablename = 'mgdrea'
Begin
select * from tablename where column = variablename;
end
else
begin
select * from tablename where column != variablename;
end

 

[mkw77]

thanks for your help. I did them as two separate statements, but I will try and see if I can convert to your suggestions to reduce my code.

However I do have another question. I want to from a list of only retrieve the first two where there are many names to one Ref as below but would always want the one where lead = 1:-

Ref Name (PersonID - this is unique) Lead
AAA Mr Apple 12355 1
AAA Mrs Apple 23455 0
AAA Ms Apple 33434 0
BBB Mr Orange 23341 1
CCC Ms Peach 23434 1
DDD Mrs Jones 90033 0
DDD Ms Jones 89383 0
DDD Mr Jones 90345 1
EEE Mrs Blue 49582 1
EEE Master Blue 78437 0
EEE Mr Blue 89900 0
EEE Ms Blue 77635 0

I would want the output to look like this:-

Ref Name (PersonID - this is unique) Lead
AAA Mr Apple 12355 1
AAA Mrs Apple 23455 0
BBB Mr Orange 23341 1
CCC Ms Peach 23434 1
DDD Mrs Jones 90033 0
DDD Mr Jones 90345 1
EEE Mrs Blue 49582 1
EEE Master Blue 78437 0
EEE Mr Blue 89900 0
FFF Ms Green 77635 1
FFF Mrs Green 93435 0

Whats the best statement to do this with. Any help would be much appreciated. Thanks in advance.

 

[NastiaMurzin]

select * from tablename order by personid asc;
try it!

 

[drlex]

2 Q.s
1)What version of SQL Server are you using?
2)How do you select the two names within a reference with more than 2 members? I understand that you want the one with Lead = 1 but where you have

DDD      Mrs Jones  90033                       0
DDD      Ms Jones   89383                       0
DDD      Mr Jones   90345                       1

what makes you choose 90033 over 89383? For the AAA, you chose 23455 over 33434, so it can't be personID related. Could you ever have 3 members of a reference with Lead = 1?

soi là, soi carré

 

[mkw77]

Hi,
Thanks I have now managed to resolve this query. asc did work but I carried out a select followed by a sub select retrieving the MAX(personid) where lead = 0 - I then just picked that one from the list as it didn't matter which one I choose.
This is on SQLSERVER 2005 by the way.
Cheers