comparing number of different length

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

SQL Server 2005 Forums

Transact-SQL (2005)

comparing number of different length

Author

Topic

ahmadjamalkhan
Starting Member

36 Posts

Posted - 2009-04-23 : 11:37:57

I have two tables , and each table has two rows

Table-A
countrycode;price
92;10
923;15
Table-B
countrycode;price
92;11
923;16
92300;15

In order to compare prices I created a Table-X inner join it with Table-A, Table-B codes and rates, and i get a following

Table-x Table-A Table-B
92 10 11
923 15 16
92300 15
92345 16

How can I compare the rest of the codes which arent common in all.

The way I was thinking is somehow populate the missing codes in each tables, thus when i will inner join it , i would be able to compare all of them. Only question remain how to populate the codes which are missing. Like in Table-A , 92 is 10 and 923 is 15 , next is 92300 which has no price , meaning its price is same as 923, thus i want to get 923 prices in all of the remaining for Table- A.

For Table-B it has 92 = 11 , 923 = 16 and 92300 as 15 , missing is 92345 best match is again 923 and even for 92333

Table-x , Table-A , Table-B
92 , 10, 11
923 , 15, 16
92300, 15, 15
92345, 15, 16
92333, 15, 16

I am sorry if i have sound it very complicated , I am not sure how to go around this problem. I would appreciate if any one can help me with this.

Thanks

notmyrealname

98 Posts

Posted - 2009-04-23 : 13:46:59

Hi ahmadjamalkhan.

I'm not exactly sure what you want but this might help. The tricky part is "guessing" what to use for a price if one doesn't exist. I have decided to look for all prices with similar countrycodes and sort by the length of the countrycode. Then i grabbed the price from the countrycode that was the longest.


SELECT     COALESCE (TableA.countrycode, TableB.countrycode) AS countrycode, ISNULL(TableA.price,
                          (SELECT     TOP (1) price
                            FROM          TableA AS TableA_1
                            WHERE      (NOT (price IS NULL)) AND (CAST(countrycode AS char(10)) LIKE CAST(countrycode AS char(10)) + '%')
                            ORDER BY LEN(countrycode) DESC)) AS Price_A, ISNULL(TableB.price,
                          (SELECT     TOP (1) price
                            FROM          TableB AS TableB_1
                            WHERE      (NOT (price IS NULL)) AND (CAST(countrycode AS char(10)) LIKE CAST(countrycode AS char(10)) + '%')
                            ORDER BY LEN(countrycode) DESC)) AS Price_B
FROM         TableA FULL OUTER JOIN
                      TableB ON TableA.countrycode = TableB.countrycode
ORDER BY countrycode

Hope it helps.

ahmadjamalkhan
Starting Member

36 Posts

Posted - 2009-05-05 : 13:20:48

Hi Thanks for your post . I think its working now .But its not getting the right result or unless i failed to explained it right. by comparing two table. this is what i am getting
table A
92,10.0
923,11.0
92300,9.5

table B
92,10.0
923,11.0
92300,10.0
92333,11.0 ( this code is not present in tableA)
92345,11.0 ( this code is not present in tableA)

When i ran the query i got the following result

countrycode,priceA, priceB
92,10.0,10.0
923,11.0,12.0
92300,9.5,10.0
92333,9.5,11.0 ( Since table A did not had 92333 code , therefore it matched to longest which was 92300)
92345,9.5,11.0 ( Since table A did not had 92345 code , therefore it matched to longest which was 92300)

but i want to match with closet match rather the longest match. if you have 92345 ,longest match is 92300 coz both are 5 character long but closet match would be 923

Is there a way i can reach to closet match.

Thanks

ahmadjamalkhan
Starting Member

36 Posts

Posted - 2009-05-07 : 06:07:17

Hi Anyone could help me with this.
The above compares the tables and find the longest match . But I want closet match.
for example

A
AA
AAA
AAAB

now if i have to find the closet match to AAAC , it would be AAA but longest match would be AAAB
I hope this explains the difference between closet and longest match.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-07 : 12:38:14

seems like what you need is this

http://sqlblindman.googlepages.com/fuzzysearchalgorithm

ahmadjamalkhan
Starting Member

36 Posts

Posted - 2009-05-07 : 13:50:52

wow! its amazing but i am newbee here . I think this code was for alphanumeric . If i put two numbers to compare its return some number.
Have u used this algoritham before. Can it be used for comparing numbers rather alphanumeric.
Thanks for ur help

ahmadjamalkhan
Starting Member

36 Posts

Posted - 2009-05-08 : 07:16:25

Hi just thinking , if a table has 92,923,92300 and i want to compare 92345 price with this table. since it does not exit so i will get NULL
but what if i try with running a loop which reduces the length of a string like first it would run with 92345 and look for match , if not
it will continue and omit the last number and re run and compare it with 9234 , if it found a match loop will exit , else it would continue till it find a match,
since 9234 does not exit , loop will continue and will omit one more number and now it would be 923 , now it will compare 923 with table and now we will have a match.

I am just trying to create a logic , if you can help would be great. Thanks

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-05-08 : 10:48:01

I'm thinking ahmadjamalkhan is wanting a flexible "begins with" search, finding the closest match, rather than my "fuzzy search" algorithm which searches entire strings.

Try this:


set nocount on

create table #TableA (CountryCode varchar(10), PriceA int)
create table #TableB (CountryCode varchar(10), PriceB int)

insert into #TableA values ('92', 10)
insert into #TableA values ('923', 15)
insert into #TableB values ('92', 11)
insert into #TableB values ('923', 16)
insert into #TableB values ('92300', 15)

;with CountryCodeCompare as
	(select	#TableB.CountryCode,
		#TableA.PriceA,
		#TableB.PriceB,
		len(#TableA.CountryCode) Specificity
	from	#TableA
		inner join #TableB on #TableB.CountryCode like #TableA.CountryCode + '%')
select	CountryCodeCompare.CountryCode,
	CountryCodeCompare.PriceA,
	CountryCodeCompare.PriceB
from	CountryCodeCompare
	inner join --BestMatches
		(select	CountryCode,
			max(Specificity) Specificity
		from	CountryCodeCompare
		group by CountryCode) BestMatches
		on CountryCodeCompare.CountryCode = BestMatches.CountryCode
		and CountryCodeCompare.Specificity = BestMatches.Specificity

drop table #TableA
drop table #TableB

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

ahmadjamalkhan
Starting Member

36 Posts

Posted - 2009-05-09 : 14:39:02

Hi Thanks for your help.
While I am trying to run I am getting error is there something missing in this line
;with CountryCodeCompare as

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-10 : 02:23:56

whats the error that you got?

ahmadjamalkhan
Starting Member

36 Posts

Posted - 2009-05-10 : 10:26:31

incorrect syntax near keywork with
;with CountryCodeCompare as

i copied the entire code in sql analyzer and executed it . Unless I need to do in parts.
I am using sql server 2000 .
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-10 : 13:07:03

quote:
Originally posted by ahmadjamalkhan

incorrect syntax near keywork with
;with CountryCodeCompare as

i copied the entire code in sql analyzer and executed it . Unless I need to do in parts.
I am using sql server 2000 .
Thanks

The posted code uses CTE.CTE is not available in sql 2000.so use a temporary table instead.


create table #TableA (CountryCode varchar(10), PriceA int)
create table #TableB (CountryCode varchar(10), PriceB int)

insert into #TableA values ('92', 10)
insert into #TableA values ('923', 15)
insert into #TableB values ('92', 11)
insert into #TableB values ('923', 16)
insert into #TableB values ('92300', 15)

select	#TableB.CountryCode,
		#TableA.PriceA,
		#TableB.PriceB,
		len(#TableA.CountryCode) Specificity 
into #CountryCodeCompare 
	from	#TableA
		inner join #TableB on #TableB.CountryCode like #TableA.CountryCode + '%'
select	CountryCodeCompare.CountryCode,
	CountryCodeCompare.PriceA,
	CountryCodeCompare.PriceB
from	#CountryCodeCompare #c
	inner join --BestMatches
		(select	CountryCode,
			max(Specificity) Specificity
		from	#CountryCodeCompare
		group by CountryCode) BestMatches
		on #c.CountryCode = BestMatches.CountryCode
		and #c.Specificity = BestMatches.Specificity

drop table #TableA
drop table #TableB
drop table #CountryCodeCompare

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-10 : 13:13:38

Also in future, please try to post in relevant forums. You have posted this in 2005 forum though you're using 2000

ahmadjamalkhan
Starting Member

36 Posts

Posted - 2009-05-11 : 08:29:21

hI visakh. Thanks for the update. Yes i am worry I did not realise while i was posting the thread.
the code you gave above , is it for sql 2000 . because when i copied and pasted in sql its giving error
about countrycodecompare do not match with any table or alias.

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2009-05-11 : 10:10:02

Be sure countrycodecompare is referencing the temp table. Prefix it with # - select #CountryCodeCompare.CountryCode,
#CountryCodeCompare.PriceA,
#CountryCodeCompare.PriceB

Terry

-- Procrastinate now!

ahmadjamalkhan
Starting Member

36 Posts

Posted - 2009-05-11 : 11:10:17

No joy yet. I have even installed SQL Server 2005 for trial.
What i have done is created Following tables

TableA ( CountryCode , PriceA)
TableB ( CountryCode , PriceB)
CountryCodeCompare ( CountryCode,PriceA,PriceB)

Then I used the following command to insert values into the tables

insert into TableA values ('92', 10)
insert into TableA values ('923', 15)
insert into TableB values ('92', 11)
insert into TableB values ('923', 16)
insert into TableB values ('92300', 15)

Then i tried running the following query

select TableB.CountryCode,
TableA.PriceA,
TableB.PriceB,
len(TableA.CountryCode) Specificity
into CountryCodeCompare
from TableA
inner join TableB on TableB.CountryCode like TableA.CountryCode + '%'
select CountryCodeCompare.CountryCode,
CountryCodeCompare.PriceA,
CountryCodeCompare.PriceB
from CountryCodeCompare c
inner join --BestMatches
(select CountryCode,
max(Specificity) Specificity
from CountryCodeCompare
group by CountryCode) BestMatches
on c.CountryCode = BestMatches.CountryCode
and c.Specificity = BestMatches.Specificity

But I get following erros!!

Msg 107, Level 16, State 3, Line 8
The column prefix 'CountryCodeCompare' does not match with a table name or alias name used in the query.
Msg 107, Level 16, State 3, Line 8
The column prefix 'CountryCodeCompare' does not match with a table name or alias name used in the query.
Msg 107, Level 16, State 3, Line 8
The column prefix 'CountryCodeCompare' does not match with a table name or alias name used in the query.

I feel so stupid , but I am still not sure what I am doing wrong.

Thanks for your help

ahmadjamalkhan
Starting Member

36 Posts

Posted - 2009-05-12 : 07:22:42

Hi
I will appreciate if someone can help me with this query above. I am not sure why it is giving me errors in both sql server 2000 and 2005.
Thanks in advnace

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-05-12 : 09:26:55

Because you have aliased your table name as "c", so you can't refer to it by its actual name anymore. Drop the alias, which is gratuitous:

select	TableB.CountryCode,
	TableA.PriceA,
	TableB.PriceB,
	len(TableA.CountryCode) Specificity
into	CountryCodeCompare
from	TableA
	inner join TableB on TableB.CountryCode like TableA.CountryCode + '%'

select	CountryCodeCompare.CountryCode,
	CountryCodeCompare.PriceA,
	CountryCodeCompare.PriceB
from	CountryCodeCompare
	inner join --BestMatches
		(select	CountryCode,
			max(Specificity) Specificity
		from	CountryCodeCompare
		group by CountryCode) BestMatches
	on CountryCodeCompare.CountryCode = BestMatches.CountryCode
	and CountryCodeCompare.Specificity = BestMatches.Specificity

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-05-12 : 09:27:36

But if you are trying it on 2005, use my original CTE method instead.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

ahmadjamalkhan
Starting Member

36 Posts

Posted - 2009-05-12 : 10:17:04

Great.Its working. You are geneious. Cheers

ahmadjamalkhan
Starting Member

36 Posts

Posted - 2009-05-12 : 10:39:27

Another thing. would you recommend any good book for sql server 2005. Also for Integration Services.
Thanks

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources