Get Preferred Address Problem

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:

PersonsID    PhoneNumber

    1                     1
    2                     6
    3                     9

This is a typical scenario in many of the applications and the possible solutions are interesting:

Solution 1:

select a.* from personphonenumber a join
(select personid,max(
case phonenumbertype
when 'home' then 4
when 'business' then 3
when 'cell' then 2
when 'fax' then 1 else 0 end) notype
from personphonenumber
group by personid) b
on a. personid = b.personid and
a.phonenumbertype =

    case b.notype

    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 address.

Solution 2:

SELECT
      PersonID, 1*right(IDPlus,10) as PhoneNumberID
FROM (
select
     PersonID,
     min(10000000000*charindex(PhoneNumberType,'home|business|cell|fax') + PhoneNumberID) as IDPlus
     from PersonPhoneNumber
     group by PersonID
) Derived

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 value.