Finding closest match - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Finding closest match

Author

Topic

nhess80
Yak Posting Veteran

83 Posts

Posted - 2010-02-16 : 16:57:28

Hi,

I am having some trouble with this. I am trying to find the closest match. See example below

Declare @CDR table(TermNumber bigint,OrigNumber bigint,CDRS varchar(50))
insert into @CDR select 2177282827,2177281212,'CDRS'
insert into @CDR select 2177281827,2177251212,'CDRS'
insert into @CDR select 2177282827,2012202212,'CDRS'

Declare @LergData Table (npanxxy bigint,[state] varchar(3))

insert into @LergData select 2177282,'IL'
insert into @LergData select 217728,'IL'
insert into @LergData select 2177252,'IL'
insert into @LergData select 217725,'IL'
insert into @LergData select 201220,'NJ'
insert into @LergData select 2012202,'NY'

SELECT OrigNumber,TermNumber,b.state,c.state,(CASE WHEN b.state = c.state THEN 'Intra' ELSE 'Inter' END)

FROM @CDR AS a LEFT OUTER JOIN
@LergData AS b ON Convert(varchar(7),b.npanxxy) = LEFT(a.OrigNumber,6) LEFT OUTER JOIN
@LergData AS c ON Convert(varchar(7),c.npanxxy) = LEFT(a.TermNumber,6)

The last result that is output should be NY since it is the closest match. I realize this won't happen with how I am using LEFT(a.TermNumber,6) but if I were to change it to 7 I will get some NULL values returned and I understand why this is but I am just having trouble figuring out a way to make this work like I want it to. Please let me know if I am unclear in what I’m looking for.

Thanks for your help,

Nick

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-02-16 : 19:19:52

Can you list the results that you would like to see?

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-17 : 00:19:30

[code]
Declare @CDR table(TermNumber bigint,OrigNumber bigint,CDRS varchar(50))
insert into @CDR select 2177282827,2177281212,'CDRS'
insert into @CDR select 2177281827,2177251212,'CDRS'
insert into @CDR select 2177282827,2012202212,'CDRS'

Declare @LergData Table (npanxxy bigint,[state] varchar(3))

insert into @LergData select 2177282,'IL'
insert into @LergData select 217728,'IL'
insert into @LergData select 2177252,'IL'
insert into @LergData select 217725,'IL'
insert into @LergData select 201220,'NJ'
insert into @LergData select 2012202,'NY'

SELECT c.*,l.npanxxy,l.[state]
FROM @CDR c
CROSS APPLY (SELECT TOP 1 npanxxy,[state]
FROM @LergData
WHERE CAST(OrigNumber AS varchar(15)) LIKE CAST(npanxxy AS varchar(15)) + '%'
ORDER BY LEN(npanxxy) DESC) l

output
---------------------------------------------
TermNumber OrigNumber CDRS npanxxy state
2177282827 2177281212 CDRS 217728 IL
2177281827 2177251212 CDRS 217725 IL
2177282827 2012202212 CDRS 2012202 NY

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nhess80
Yak Posting Veteran

83 Posts

Posted - 2010-02-19 : 13:00:01

Thank you...This works out perfect

quote:

Originally posted by visakh16


Declare @CDR table(TermNumber bigint,OrigNumber bigint,CDRS varchar(50))
insert into @CDR select 2177282827,2177281212,'CDRS'
insert into @CDR select 2177281827,2177251212,'CDRS'
insert into @CDR select 2177282827,2012202212,'CDRS'


Declare @LergData Table (npanxxy bigint,[state] varchar(3))

insert into @LergData select 2177282,'IL'
insert into @LergData select 217728,'IL'
insert into @LergData select 2177252,'IL'
insert into @LergData select 217725,'IL'
insert into @LergData select 201220,'NJ'
insert into @LergData select 2012202,'NY'

SELECT c.*,l.npanxxy,l.[state]
FROM @CDR c
CROSS APPLY (SELECT TOP 1 npanxxy,[state]
             FROM @LergData
             WHERE CAST(OrigNumber AS varchar(15)) LIKE CAST(npanxxy AS varchar(15)) + '%'
             ORDER BY LEN(npanxxy) DESC) l

output
---------------------------------------------
TermNumber	OrigNumber	CDRS	npanxxy	state
2177282827	2177281212	CDRS	217728	IL
2177281827	2177251212	CDRS	217725	IL
2177282827	2012202212	CDRS	2012202	NY

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-19 : 13:04:02

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nhess80
Yak Posting Veteran

83 Posts

Posted - 2010-02-22 : 12:56:58

Now that I have used this code and rewritten it for my use it runs super slow. Just so you know the tables that will be used in this query are large. At minimum there will be 4 tables being used. In the largest table I will be searching over a month span which is about 5 to 10 million records the other tables have about 140,000 records a piece. I the cross apply not meant for large amounts of data? Below is my query. I made it any easy one where I am only search over 1 min time span. It only returns 13 records and it took 5 seconds to run. Maybe I’m using the cross apply wrong.

SELECT a.*,b.[state],c.[state],CASE WHEN b.state = c.state THEN 'Intra' ELSE 'Inter' END AS InterIntra,

CASE WHEN b.state = c.state THEN
CASE WHEN ZoneSIP_intra IS NULL THEN NULL
ELSE ZoneSIP_intra END
ELSE
CASE WHEN ZoneSIP_inter IS NULL THEN NULL
ELSE ZoneSIP_inter END
END AS RateSelected,

CASE WHEN b.state = c.state THEN
CASE WHEN ZoneSIP_intra IS NULL THEN NULL
ELSE 'ZoneSIP_intra' END
ELSE
CASE WHEN ZoneSIP_inter IS NULL THEN NULL
ELSE 'ZoneSIP_inter' END
END AS VendorSelected
FROM CDRS a
CROSS APPLY (SELECT TOP 1 npanxxy,[state]
FROM LergData
WHERE CAST(ANI AS varchar(15)) LIKE CAST(npanxxy AS varchar(15)) + '%'
ORDER BY LEN(npanxxy) DESC) b

CROSS APPLY (SELECT TOP 1 npanxxy,[state]
FROM LergData
WHERE CAST(LRN AS varchar(15)) LIKE CAST(npanxxy AS varchar(15)) + '%'
ORDER BY LEN(npanxxy) DESC) c

CROSS APPLY (SELECT TOP 1 ZoneSIP_npanxx,ZoneSIP_inter,ZoneSIP_intra
FROM ZoneSIP
WHERE CAST(TermNumber AS varchar(15)) LIKE CAST(ZoneSIP_npanxx AS varchar(15)) + '%'
ORDER BY LEN(ZoneSIP_npanxx) DESC) ZoneSIP

Where (DateTimeInt Between 1266224400 AND 1266224459)

quote:
Originally posted by visakh16

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources