Getting Preferred Address Problem ?
I was answering to the microsoft.public.newsgroups
when I got this strange request. We did have numerous solutions posted but
there was a solution posted by Steve Kass that stumped me. I thought of posting
the solution to my tips and tricks section.
Q: Basically the question is to get the most
appropriate address for a person. Every person can have multiple addresses like
Home, Billing, Fax, cell etc. Our requirement is to get the Home address first
failing which we need to get the business address failing which we need to get
the cell else finally get the Fax number. So we need to get just the
appropriate number for the person.
For this example take the following script to get a
try at the same before taking the solution.
create table Person (
PersonID int )
create table PersonPhoneNumber (
PersonID int ,PhoneNumberID int
,PhoneNumberType varchar (10) )
insert into person values (1)
insert into person values (2)
insert into person values (3)
Insert into PersonPhoneNumber
values (1, 1, 'home')
Insert into PersonPhoneNumber values (1, 2, 'business')
Insert into PersonPhoneNumber values (1, 3, 'cell')
Insert into PersonPhoneNumber values (1, 4, 'fax')
Insert into PersonPhoneNumber values (2, 5, 'fax')
Insert into PersonPhoneNumber values (2, 6, 'business')
Insert into PersonPhoneNumber values (3, 7, 'cell')
Insert into PersonPhoneNumber values (3, 8, 'fax')
Insert into PersonPhoneNumber values (3, 9, 'business')
So we need to get a output as below:
This is a typical scenario in many of the applications
and the possible solutions are interesting:
a.* from personphonenumber a join
when 'home' then 4
when 'business' then 3
when 'cell' then 2
when 'fax' then 1 else 0 end) notype
group by personid) b
on a. personid = b.personid and
when 4 then 'home'
when 3 then 'business'
when 2 then 'cell'
when 1 then 'fax' end
What we do here is give pseudo numbers to the
address types that interest us and use the first one to return the actual
PersonID, 1*right(IDPlus,10) as
+ PhoneNumberID) as IDPlus
Let me walk you across the pseudo logic. Look we have
the actual order in a string to compare. And we compare the string with the
available address types for the person. If the value is smaller that is the
lowest available address type available for the person. Hence we use that