Choosing a phone number from list with rules

[sxschech]

I would like to join the telephone table to the person table to display one record per person and a particular telephone number. The rule is that if the telephone has a code of MA, choose that, else if has a code of PR and Indicator="Y" choose that, if no indicator, choose the highest sequence number.

Telephone Data Example:
[tt]
ID Phone_Type Phone_Number Phone_Indic Phone_Seq
123 MA 5551212 2
123 PR 5552222 1
123 PR 5552211 3
321 PR 5559988 1
321 PR 5559987 2
444 MA 5556633 2
444 PR 5556634 Y 1
[/tt]

Result needed:
[tt]
123 MA 5551212
321 PR 5559987
444 MA 5556633
[/tt]

Full Result set
P.ID, P.Name, T.Phone_Number, P.Level, etc...

Below is part of the query I tried, but it gave error that it was returning more than one row. Hopefully there is a more efficient way to write the query. I used multiple case statements as wasn't sure how to combine into one phone number selection.

select distinct ID, Name, 
       Case When Phone_Type='MA' And Phone_Number_Combined is not null Then Phone_Number_Combined END MA,
       Case When Phone_Type='PR' And Phone_Number_Combined is not null And Phone_Primary_Ind='Y' Then Phone_Number_Combined END PRY, 
       Case When Phone_Type='PR' And Phone_Number_Combined is not null Then Phone_Number_Combined END PR,
       Case When Phone_Type='PR' And Phone_Number_Combined is not null Then 
        (
        SELECT Phone_Number_Combined From Telephone T
         Where Phone_Seq_Number = 
               (Select Max(Phone_Seq_Number)
                  From Telephone
                  Where ID = T.ID)
        ) PRMax,
       Phone_Primary_Ind, phone_seq_number
       from telephone
       where Phone_Type in ('MA','PR');

 

[AlexCuse]

sxschech - I don't have anything to test with on this machine, but I think this will work for you:

select x.ID, x.PHONE_TYPE, x.PHONE_NUMBER
from PHONE_DATA x
where x.PHONE_NUMBER = (
	select top 1 PHONE_NUMBER
	from PHONE_DATA
	where ID = x.ID
	order by case when PHONE_TYPE = 'MA'then 999
		when PHONE_TYPE = 'PR' and PHONE_INDIC = 'Y' then 998
		else PHONE_SEQ end desc
)

It assumes your PHONE_SEQ will never reach 998, if this is not the case then just increase the values. Basically what you want to do is use the case expression in your subquery to create a column to order by (descending), and force your two 'special cases' to the top, and use PHONE_SEQ if it is not a special case.

I am sure this can be done with a self join or two as well, but it might not perform that much better because the subqueries should be relatively small.

Hope this helps,

Alex

[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom

 

[r937]

alex, tsk tsk

this is the ANSI SQL forum

"TOP" is a microsoft abomination

;-)

r937.com | rudy.ca

 

[AlexCuse]

Ok, try this:

select x.ID, x.PHONE_TYPE, x.PHONE_NUMBER
from PHONE_DATA x
inner join (
	select z.ID, z.PHONE_TYPE, z.PHONE_NUMBER
	from PHONE_DATA z
	where case when PHONE_TYPE = 'MA'then 999
        when PHONE_TYPE = 'PR' and PHONE_INDIC = 'Y' then 998
        else PHONE_SEQ end
			= (select max(case when PHONE_TYPE = 'MA'then 999
				when PHONE_TYPE = 'PR' and PHONE_INDIC = 'Y' then 998
				else PHONE_SEQ end)
				from PHONE_DATA
				where ID = z.ID )
) sq
on x.ID = sq.ID
and x.PHONE_TYPE = sq.PHONE_TYPE
and x.PHONE_NUMBER = sq.PHONE_NUMBER

Depending on your data, you may end up needing a distinct in there (the outer query) but I kind of doubt it.

Hope it helps,

Alex

[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom