Comparing Ranges - 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 2005 Forums

Transact-SQL (2005)

Comparing Ranges

Author

Topic

fubuki99
Starting Member

10 Posts

Posted - 2009-05-22 : 16:53:34

Greetings,

I have two tables, one called Customer and the other CellRange which contains the following columns and row information:

Table CellRange:

E2Vendor cs_npa cs_nxx cs_callnumlow cs_callnumhigh

E2Intra 713 211 0560 0569

E2Intra 281 211 3426 3440

E2TCS 713 511 3900 3909

E2TCS 281 211 0430 0439

E2VIXXI 281 211 3083 3097

Customer table

TN_Number CompID

713-211-0560 LEAP
713-211-0566 LEAP
713-211-0568 LEAP
281-211-0430 LEAP
281-211-0437 LEAP
713-511-3572 TMOB
281-211-3087 VIXXI

Can I get some help in writing a statement that will allow me to review the data from the Customer table that matches the range of the CellRange table based on the "TN_Number" column that compares to the range columns "cs_npa", "cs_nxx" , "cs_callnumlow" and "cs_callnumhigh" from the CellRange table. I have thousands of rows in the customer table that should have numbers within the range of the CellRange table. I would like the output to show me the "TN_Number" and "CompID" from the customer table that match between any of the ranges within the CellRange table and associate that customer data with the corresponding E2Vendor column.

e.g Output result:

Number CompID E2Vendor

713-211-0560 LEAP E2Intra

I would also like the fallout "Nonmatching TN_Number to Range" on any TN_Number to be associated with an "E2Vendor", if there isn't one that matches any criteria , I suppose it should by default return "Null".

e.g Output result:

Number CompID E2Vendor

713-511-3572 TMOB NULL

Thanks you for your help in advance, I'm not proficient in T-SQL so would appreciate the help.

kira
Starting Member

17 Posts

Posted - 2009-05-22 : 17:20:31

[CODE]
select c.TN_Number, c.CompID, d.E2Vendor
from
customer c
left outer join
( select
a.TN_number,
a.CompID,
b.E2Vendor
from
customer a
inner join cellrange b
ON
CAST(left(a.TN_Number,3) as int) = b.cs_npa
AND
CAST(substring(a.TN_Number,5,3) as int) = b.cs_nxx
AND
CAST(substring(a.TN_Number,9,4) as int) between cs_callnumlow AND cs_callnumhigh
) d
on c.TN_number = d.TN_number

[/CODE]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-23 : 11:25:42

create a function like this


CREATE FUNCTION ParseValues  
(@String varchar(8000)  
)  
RETURNS @RESULTS TABLE  
(ID int identity(1,1),  
Val int  
)  
AS  
BEGIN  
DECLARE @Value varchar(100)  
  
WHILE @String is not null  
BEGIN  
SELECT @Value=CASE WHEN CHARINDEX(',',@String) >0 THEN LEFT(@String,CHARINDEX(',',@String)-1) ELSE @String END,  
@String=CASE WHEN CHARINDEX(',',@String) >0 THEN SUBSTRING(@String,CHARINDEX(',',@String)+1,LEN(@String)) ELSE NULL END  
INSERT INTO @RESULTS (Val)   
SELECT @Value   
END  
RETURN  
END

then use it like below:-
SELECT m.TN_Number,m.CompID,v.E2Vendor
FROM
(
SELECT MAX(CASE WHEN f.ID = 1 THEN f.Val ELSE NULL END) AS cs_npa,
MAX(CASE WHEN f.ID = 2 THEN f.Val ELSE NULL END) AS cs_nxx,
MAX(CASE WHEN f.ID = 3 THEN f.Val ELSE NULL END) AS cs_callnum,
c.CompID,c.TN_Number
FROM Customer c
CROSS APPLY dbo.ParseValues('-',c.TN_Number) f
GROUP BY c.CompID,c.TN_Number
)m
OUTER APPLY (SELECT  E2Vendor
             FROM CellRange
             WHERE cs_npa=m.cs_npa
             AND cs_nxx=m.cs_nxx
             AND m.cs_callnum BETWEEN cs_callnumlow AND cs_callnumhigh)v

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-05-25 : 00:35:44

Hi Try this once,

select c.Number ,c.CompID ,ct.E2Vendor from CellRange c
inner join Customer ct ON ( patindex(replace(ct.TN_Number,'-','.',1) = c.cs_callnumlow
AND patindex(replace(ct.TN_Number,'-','.',2) = c.cs_nxx
AND patindex(replace(ct.TN_Number,'-','.',3) = c.cs_npa
)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources