Solved

SQL 2008

Posted on 2012-04-11
5
156 Views
Last Modified: 2012-04-15
Hello,
I'm trying to find the zoneid, Price of a postal code within a range of 2 columns.
My Table is:
ZoneID              Price                    PostalCodeFrom                        PostalCodeTo
481                      $5.oo                    L4A0A6                                  L5B9Z9
551                      $12.00                   L2X1B2                                  L3X1W3
214                      $7.50                     L3P0A0                                 L6S9Z9

is I want to find the zoneid,Price of postal codes,
example: L4B2E2,  L2X3X5, L5R2B4

Any Help is appreciated.
Thanks,
0
Comment
Question by:W.E.B
  • 2
  • 2
5 Comments
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 75 total points
ID: 37836024
You're looking for the BETWEEN operator:

DECLARE @PostalCode VARCHAR(6)
   SET @PostalCode = 'L4B2E2'

SELECT ZoneID, Price
  From YourTable
 WHERE @PostalCode BETWEEN PostalCodeFrom AND PostalCodeTo

Open in new window


BETWEEN finds values that are >= the first and <= the second, which it sounds like is what you want to do.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 37837040
@ryanmccauley - very close, but I think there is more to it than that - or - the sample data might be a bit out.

Alphabetically there are overlapping postalcodes, so, the query might return more than 1 row. The first example "L4B2E2" would return zones 481 and 214

So, the query needs a bit more qualification like TOP 1 based on price, zone or something else... What is the defining uniqueness that will give us a single answer ?

Then we also need to consider how it is to be used. How is the price lookup needed ? Is it a straight query, or maybe a subquery (or procedure or function) returning the value for some other data source ?

I think the best way to answer this is to first have a couple of the above questions answered first.
0
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 37837992
I'd hope that the ranges are exclusive, but I suppose you're right - given the data above, you can't really tell. Hopefully the OP is back soon to clear things up.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 37838848
Agree...

And here is some sample code just to demonstrate some of the questions I raise above...

-- OK so, first lets create some test data 
-- For this we will use TEMPDB database and some (hopefully) *unique* table names

use tempdb
GO

-- first check for our test tables and remove so we can start afresh

if object_id('tmp_postal_prices','U') is not null drop table tmp_postal_prices
if object_id('tmp_things_to_post','U') is not null drop table tmp_things_to_post
GO

create table tmp_postal_prices (zoneid int, price money, PostalCodeFrom varchar(20), PostalCodeTo varchar(20))
GO

-- we really should create an index depending on performance and volumes

create index idx_tmp_postal_prices_postalcodes on tmp_postal_prices (PostalCodeFrom, PostalCodeTo)
GO

-- now load some data

insert tmp_postal_prices
select 481,5.oo,'L4A0A6','L5B9Z9' union all
select 551,12.00,'L2X1B2','L3X1W3' union all
select 214,7.50,'L3P0A0','L6S9Z9'
GO

-- Now we can do a select with the "between" using one of the examples from: L4B2E2,  L2X3X5, L5R2B4

select Zoneid, Price 
from tmp_postal_prices 
where 'L4B2E2' between PostalCodeFrom and PostalCodeTo
GO
/*-- returns two rows - which maybe OK - but sounds like we want a (one) price.
481	5.00
214	7.50
*/

-- So, now we do a select but this time restricting the row selection
-- (and this is just one example, there can be other methods)

select top 1 Zoneid, Price 
from tmp_postal_prices 
where 'L4B2E2' between PostalCodeFrom and PostalCodeTo
Order by Price desc 
GO
/*-- returns a single row
214	7.50
*/

-- To deomnstrate the second question, ie "how to use" 
-- This time we will build another table containing information we want to price.

create table tmp_things_to_post (TID int, Item varchar(60), destination varchar (20))
GO

insert tmp_things_to_post 
select 1,'A big box','L4B2E2' union all
select 2,'A big brown paper bag','L5R2B4' union all
select 2,'A thing with 2x and 3x','L2X3X5' 
GO

-- now lets try to "price" our postal data...

Select t.*, p.zoneid, p.price
from tmp_things_to_post T
outer apply (select top 1 Zoneid, Price 
             from tmp_postal_prices 
             where t.destination between PostalCodeFrom and PostalCodeTo
             Order by Price desc ) P

-- and the above is only one method from various other methods available.
-- we could do an inline query
-- we could create a function
-- we could bundle it all in a Stored Procedure
-- we do need to consider indexes in all of this too 
--    (otherwise a possible scan everytime - ouch)

-- and now we can cleanup

if object_id('tmp_postal_prices','U') is not null drop table tmp_postal_prices
if object_id('tmp_things_to_post','U') is not null drop table tmp_things_to_post
GO

Open in new window

0
 

Author Closing Comment

by:W.E.B
ID: 37848847
Hello,
using the Select --  Between, will return few hundred results per search, (this is what I was trying to avoid), What I did is, used the Select -- between , and I put some case if into my script, this way I can narrow the results,

I'm getting 3  results right now (which is not bad) vs few hundreds.

Thanks for your help.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
T-SQL Stored Procedure - Search multiple tables (Single column) for similar values 7 62
Caste datetime 2 52
SQL 2008 Conversion failed 7 20
SQL Server Question 5 28
Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now