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
 Finding if a value falls within several ranges

Author  Topic 

Wombat
Starting Member

3 Posts

Posted - 2013-02-12 : 21:47:04
G'day,
I am now to this forum and I am seeking some help please>
I am trying to do some analysis of telephone numbers dialed from our sites to determine how many are going to other company sites.
We have 35,000 extensions and over 400 sites spread across the State.
At each site we typically ask for a range of telephone numbers from the TELCO who supply them in blocks of 100 numbers. THe block of numbers in the ranfge are contiguous but the ranges may not be adject to each other. For instance id I asked for 300 numbers I might get allocated 65000-65099, 66200-66299, 66800-66899.
At the moment we have > 2000 of these ranges and they are continually added and deleted as we open/close or relocate premises

I am getting about 18 Million calls per qtr and would like to ascertain if the number recorded by the PABX as being dialed is to one of our sites or External (i.e gone to the outside world).


Min Number Max Number Site
0 70717399 External
70717400 70717499 CYH - The Second Story
70717500 71292299 External
71292300 71292399 Modbury
71292400 73228399 External
73228400 73228499 SADS Dental
73228500 74249999 External
74250000 74250499 Repatriation General
74250500 74252799 External
74252800 74252899 SABME Torrensville
74252900 74252999 External

The result I am looking for is to compare the list of numbers dialed against the range to see if it is one of out sites and the site name

Number-Dialled Site
74250501 External
74252810 SABME Torrensville

I am trying to do this without having to make all the dummy entries that I have currently shown to facilitate a VLOOKUP in Excel.

Any help would be greatly appreciated.

Cheers.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-02-12 : 23:33:45
[code]
SELECT d.Number, s.Site
FROM numbers_dialed d
CROSS APPLY
(
SELECT Site
FROM numbers_list x
WHERE x.Min <= d.Number
AND x.Max >= d.Number
) s
[/code]


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

Go to Top of Page

Wombat
Starting Member

3 Posts

Posted - 2013-02-13 : 02:18:31
Fantastic KH!!!!!

It took a bit of mucking about on my end but eventually I was able to get the result.

I hadn't seen a CROSS APPLY before.

Thank you ever so much!!

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-13 : 05:11:46
quote:
Originally posted by Wombat

Fantastic KH!!!!!

It took a bit of mucking about on my end but eventually I was able to get the result.

I hadn't seen a CROSS APPLY before.

Thank you ever so much!!




More applications of APPLY here

http://visakhm.blogspot.in/2010/01/multipurpose-apply-operator.html

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

Go to Top of Page
   

- Advertisement -