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

Posted in: Getting Preferred Address Problem