I have big list of international phone codes and a smaller rate sheet from a phone company. I need to assign rates to my list. The difficulty is that our lists are different. In the following example I have 4 codes in my list and only 2 codes in a phone company's list. code 4478 has to get the rate $0.01 and code 44787210 has to get the rate $0.02 Thanks.
drop table #my_list drop table #phone_company_list
select *
from #my_list m
cross apply
(
select top 1 rate
from #phone_company_list p
where convert(varchar(10), m.cc_prefix) like convert(varchar(10), p.cc_prefix) + '%'
order by cc_prefix desc
) r
Complicated conditional join
Posted - 08/06/2010 : 20:52:46
