SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Finding if a value falls within several ranges
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Wombat
Starting Member

Australia
3 Posts

Posted - 02/12/2013 :  21:47:04  Show Profile  Reply with Quote
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)

Singapore
17636 Posts

Posted - 02/12/2013 :  23:33:45  Show Profile  Reply with Quote

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



KH
Time is always against us

Go to Top of Page

Wombat
Starting Member

Australia
3 Posts

Posted - 02/13/2013 :  02:18:31  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 02/13/2013 :  05:11:46  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000