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.
| 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 premisesI 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 Site0 70717399 External70717400 70717499 CYH - The Second Story70717500 71292299 External71292300 71292399 Modbury71292400 73228399 External73228400 73228499 SADS Dental73228500 74249999 External74250000 74250499 Repatriation General74250500 74252799 External74252800 74252899 SABME Torrensville74252900 74252999 ExternalThe 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 nameNumber-Dialled Site74250501 External74252810 SABME TorrensvilleI 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.SiteFROM 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] |
 |
|
|
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!! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|
|
Finding if a value falls within several ranges