Complicated conditional join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Complicated conditional join

Author

Topic

oldfox
Starting Member

17 Posts

Posted - 2010-08-06 : 20:52:46

Hi all

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

create table #my_list
(cc_prefix numeric,
CityCountry varchar(200))
create table #phone_company_list
(cc_prefix numeric,
CityCountry varchar(200),
rate decimal(10,5))

insert into #my_list values (44,'UK DEFAULT')
insert into #my_list values (4478,'UK MMO2')
insert into #my_list values (4478721,'UNITED KINGDOM MMO2 MOBILE')
insert into #my_list values (44787210,'UNITED KINGDOM MMO2 MOBILE')

insert into #phone_company_list values (44,'UK DEFAULT',0.01)
insert into #phone_company_list values (4478721,'UNITED KINGDOM MMO2 MOBILE',0.02)

select * from #my_list
select * from #phone_company_list

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-08-06 : 23:47:05

[code]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[/code]

KH
[spoiler]Time is always against us[/spoiler]

oldfox
Starting Member

17 Posts

Posted - 2010-08-09 : 19:45:46

Thank you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources