query problem

Hi All,
I have a problems with my query on how to come up with my desired results.
Below are my two tables with sample data:
tableRegion
--
ColPrefix | ColRegion
--
0044 | UK - National
00441 | UK - Mobile
001213 | USA - California
001 | USA
tableNumbers
--
ColPhone
--
0044123456789
0044712345678
0012131234567
0018001234567
I want to come up with results displaying PhoneNumbers with their
coresponding Region based on their prefix like below. Any solutions?
0044123456789 | UK - National
0044712345678 | UK - Mobile
0012131234567 | USA - California
0018001234567 | USA
Looking for help on this one. Thanks in advance!
joelPlease post not only expected results, but fully defined DDL and sample
data. See http://www.aspfaq.com/etiquette.asp?id=5006 for more details.
Also, I believe either your expected results are wrong, or you sample data
is wrong, because it looks like 0044123456789 should match UK Mobile, not UK
National.
Lastly, I would highly recommend normalizing tableNumbers to have a column
for region prefix, since it is obvious that you want to use it as its own
atomic value.
You might want to consider a new naming convention for your tables and
columns as well - it is not considered "best practice" to name an item for
what it is physically, but for what it represents conceptually - i.e.
PhoneNumber instead of ColPhone, Region instead of ColRegion, etc. (Of
course, "Best Practices" for one person are "Worst Habits" for another!)
Enough of the nitpicking - I think I have a solution that works. It could
definitely be optimized, but it functions as is.
SELECT P1.ColPhone, P1.ColRegion
FROM
(SELECT PHN.ColPhone, REG.ColRegion, LEN(REG.ColPrefix) AS PrefixLength
FROM tableNumbers PHN INNER JOIN tableRegions REG
ON PHN.ColPhone LIKE REG.ColPrefix + '%') P1
INNER JOIN
(SELECT PHN.ColPhone, MAX(LEN(REG.ColPrefix)) AS PrefixLength
FROM tableNumbers PHN INNER JOIN tableRegions REG
ON PHN.ColPhone LIKE REG.ColPrefix + '%'
GROUP BY PHN.ColPhone) P2
ON P1.ColPhone = P2.ColPhone
AND P1.PrefixLength = P2.PrefixLength
This is based on the understanding that all longer prefixes identify more
specific areas than shorter prefixes. So if you had:
ColPrefix | ColRegion
001213 | USA - California
0012 | USA - West Coast
001 | USA
then 001213 would take precedence over 0012, and 0012 would take precedence
over 001.
IHTH
Jeremy Williams
"Joel Gacosta" <joel@.gacosta.net> wrote in message
news:eR6G6UFbFHA.3400@.tk2msftngp13.phx.gbl...
> Hi All,
> I have a problems with my query on how to come up with my desired results.
> Below are my two tables with sample data:
> tableRegion
> --
> ColPrefix | ColRegion
> --
> 0044 | UK - National
> 00441 | UK - Mobile
> 001213 | USA - California
> 001 | USA
> tableNumbers
> --
> ColPhone
> --
> 0044123456789
> 0044712345678
> 0012131234567
> 0018001234567
>
> I want to come up with results displaying PhoneNumbers with their
> coresponding Region based on their prefix like below. Any solutions?
> 0044123456789 | UK - National
> 0044712345678 | UK - Mobile
> 0012131234567 | USA - California
> 0018001234567 | USA
> Looking for help on this one. Thanks in advance!
> joel
>|||Thanks Jeremy! You're right I posted a wrong sample data.
Anyway I'll take your advise and try your solution. That is exactly what I
meant.
"Jeremy Williams" <jeremydwill@.netscape.net> wrote in message
news:%23XV4LSGbFHA.3864@.TK2MSFTNGP10.phx.gbl...
> Please post not only expected results, but fully defined DDL and sample
> data. See http://www.aspfaq.com/etiquette.asp?id=5006 for more details.
> Also, I believe either your expected results are wrong, or you sample data
> is wrong, because it looks like 0044123456789 should match UK Mobile, not
> UK
> National.
> Lastly, I would highly recommend normalizing tableNumbers to have a column
> for region prefix, since it is obvious that you want to use it as its own
> atomic value.
> You might want to consider a new naming convention for your tables and
> columns as well - it is not considered "best practice" to name an item for
> what it is physically, but for what it represents conceptually - i.e.
> PhoneNumber instead of ColPhone, Region instead of ColRegion, etc. (Of
> course, "Best Practices" for one person are "Worst Habits" for another!)
> Enough of the nitpicking - I think I have a solution that works. It could
> definitely be optimized, but it functions as is.
> SELECT P1.ColPhone, P1.ColRegion
> FROM
> (SELECT PHN.ColPhone, REG.ColRegion, LEN(REG.ColPrefix) AS PrefixLength
> FROM tableNumbers PHN INNER JOIN tableRegions REG
> ON PHN.ColPhone LIKE REG.ColPrefix + '%') P1
> INNER JOIN
> (SELECT PHN.ColPhone, MAX(LEN(REG.ColPrefix)) AS PrefixLength
> FROM tableNumbers PHN INNER JOIN tableRegions REG
> ON PHN.ColPhone LIKE REG.ColPrefix + '%'
> GROUP BY PHN.ColPhone) P2
> ON P1.ColPhone = P2.ColPhone
> AND P1.PrefixLength = P2.PrefixLength
> This is based on the understanding that all longer prefixes identify more
> specific areas than shorter prefixes. So if you had:
> ColPrefix | ColRegion
> 001213 | USA - California
> 0012 | USA - West Coast
> 001 | USA
> then 001213 would take precedence over 0012, and 0012 would take
> precedence
> over 001.
> IHTH
> Jeremy Williams
> "Joel Gacosta" <joel@.gacosta.net> wrote in message
> news:eR6G6UFbFHA.3400@.tk2msftngp13.phx.gbl...
>|||That data is pretty nasty; definately consider revising the schema to
something more workable. I went after the problem using a ranking mechanism
to select which number gets associated with which prefix. I assumed the nam
e
of the tables were [tblNumbers] and [tblRegion].
Hope this helps!
declare @.prefix_rank table (rank int, ColPrefix varchar(20), ColRegion
varchar(20))
declare @.phonelist table (ColPhone varchar(30), ColRegion varchar(20))
declare @.loop int, @.level int
select @.loop = max(len(ColPrefix)) from tblRegion
set @.level = 1
while @.loop > 0
begin
if exists(select * from tblRegion where len(ColPrefix) = @.loop)
begin
insert into @.prefix_rank (rank, ColPrefix, ColRegion)
select @.level, ColPrefix, ColRegion from tblRegion where len(ColPrefix)
= @.loop
set @.level = @.level + 1
end
set @.loop = @.loop - 1
end
set @.loop = 1
while @.loop <= @.level
begin
insert into @.phonelist (ColPhone, ColRegion)
select tn.ColPhone, pr.ColRegion
from tblNumbers tn
,@.prefix_rank pr
where tn.ColPhone like pr.ColPrefix + '%'
and tn.ColPhone not in (select ColPhone from @.phonelist)
and pr.rank = @.loop
and not exists (select pl.* from @.phonelist pl where pl.ColPhone =
tn.ColPhone)
set @.loop = @.loop + 1
end
select * from @.phonelist
"Joel Gacosta" wrote:

> Hi All,
> I have a problems with my query on how to come up with my desired results.
> Below are my two tables with sample data:
> tableRegion
> --
> ColPrefix | ColRegion
> --
> 0044 | UK - National
> 00441 | UK - Mobile
> 001213 | USA - California
> 001 | USA
> tableNumbers
> --
> ColPhone
> --
> 0044123456789
> 0044712345678
> 0012131234567
> 0018001234567
>
> I want to come up with results displaying PhoneNumbers with their
> coresponding Region based on their prefix like below. Any solutions?
> 0044123456789 | UK - National
> 0044712345678 | UK - Mobile
> 0012131234567 | USA - California
> 0018001234567 | USA
> Looking for help on this one. Thanks in advance!
> joel
>
>

Query Notification

Hi,
I have problem with SqlDependency and could not use any sample in VB.NET
2005. Some samples I found in internet, use Start mehod that I don't have in
my SqlDependency instance. And some of them use Id and Servie properties of
SqlNotificationRequest that VS IDE suggests using UserData instead of them.
I would be most grateful if anybody could post a URL to VB sample so that I
can use.
I am using SQL Server 2005 - 9.00.1187.07 with VS.NET Beta 2.0.50512
Thanks again,
Leila
BTW: Where is SQL-CLR group'!! :(You should upgrade to RTM if possible; changes to SqlDependency occurred in
the final CTP (September); I'm not sure if there were any more changes
between then and RTM.
Also, keep in mind that the Start method is static. You don't call it per
SqlDependency object, but rather for the entire application.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Leila" <Leilas@.hotpop.com> wrote in message
news:OBwjgkf5FHA.632@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have problem with SqlDependency and could not use any sample in VB.NET
> 2005. Some samples I found in internet, use Start mehod that I don't have
> in
> my SqlDependency instance. And some of them use Id and Servie properties
> of
> SqlNotificationRequest that VS IDE suggests using UserData instead of
> them.
> I would be most grateful if anybody could post a URL to VB sample so that
> I
> can use.
> I am using SQL Server 2005 - 9.00.1187.07 with VS.NET Beta 2.0.50512
> Thanks again,
> Leila
> BTW: Where is SQL-CLR group'!! :(
>|||Thanks indeed Adam,
Another question: Can I receive the notifications generated by EVENT
NOTIFICATION (such as DDL events) in my win app?
Finally I could do this for query notifications but I'd like to know if the
same thing can be done for other messages?
Leila
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23270H5m5FHA.1864@.TK2MSFTNGP12.phx.gbl...
> You should upgrade to RTM if possible; changes to SqlDependency occurred
> in the final CTP (September); I'm not sure if there were any more changes
> between then and RTM.
> Also, keep in mind that the Start method is static. You don't call it per
> SqlDependency object, but rather for the entire application.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:OBwjgkf5FHA.632@.TK2MSFTNGP10.phx.gbl...
>

Query Notification

Hi,
I have problem with SqlDependency and could not use any sample in VB.NET
2005. Some samples I found in internet, use Start mehod that I don't have in
my SqlDependency instance. And some of them use Id and Servie properties of
SqlNotificationRequest that VS IDE suggests using UserData instead of them.
I would be most grateful if anybody could post a URL to VB sample so that I
can use.
I am using SQL Server 2005 - 9.00.1187.07 with VS.NET Beta 2.0.50512
Thanks again,
Leila
BTW: Where is SQL-CLR group?!!
You should upgrade to RTM if possible; changes to SqlDependency occurred in
the final CTP (September); I'm not sure if there were any more changes
between then and RTM.
Also, keep in mind that the Start method is static. You don't call it per
SqlDependency object, but rather for the entire application.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"Leila" <Leilas@.hotpop.com> wrote in message
news:OBwjgkf5FHA.632@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have problem with SqlDependency and could not use any sample in VB.NET
> 2005. Some samples I found in internet, use Start mehod that I don't have
> in
> my SqlDependency instance. And some of them use Id and Servie properties
> of
> SqlNotificationRequest that VS IDE suggests using UserData instead of
> them.
> I would be most grateful if anybody could post a URL to VB sample so that
> I
> can use.
> I am using SQL Server 2005 - 9.00.1187.07 with VS.NET Beta 2.0.50512
> Thanks again,
> Leila
> BTW: Where is SQL-CLR group?!!
>
|||Thanks indeed Adam,
Another question: Can I receive the notifications generated by EVENT
NOTIFICATION (such as DDL events) in my win app?
Finally I could do this for query notifications but I'd like to know if the
same thing can be done for other messages?
Leila
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23270H5m5FHA.1864@.TK2MSFTNGP12.phx.gbl...
> You should upgrade to RTM if possible; changes to SqlDependency occurred
> in the final CTP (September); I'm not sure if there were any more changes
> between then and RTM.
> Also, keep in mind that the Start method is static. You don't call it per
> SqlDependency object, but rather for the entire application.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:OBwjgkf5FHA.632@.TK2MSFTNGP10.phx.gbl...
>