calculating shipping charges based on LBS range an

# Thread: calculating shipping charges based on LBS range an

1. Senior Member
Join Date
Dec 1969
Posts
708

## calculating shipping charges based on LBS range an

Hello gurus,<BR><BR>Can I ask for a big favor of you, please, please<BR><BR>I created 2 tables:<BR><BR>One table is called shipping and another is called products.<BR><BR>The products table houses all of our products including shippingWeight (text) of each product.<BR><BR>The shipping table is a lookup table for shippingWeight.<BR><BR>It has the following fields:<BR><BR>fromWeight<BR>toWeight<BR>zipCode<B R>shippingCharges<BR><BR>The layout would look like this:<BR><BR>fromWeight toWeight ZipCode shippingCharges<BR><BR>1 lb 5 lbs 35514 \$25.00<BR>6lb 10lbs 02541 \$18.00,<BR>etc <BR><BR>The maximum range is 150 lbs<BR>Anything over 150 lbs gets a fixed shipping charges rate.<BR><BR>Basically, a customer adds an item to his or her cart.<BR><BR>During checkout, the total amount of shippingWeight for all items are tabulated.<BR>Then the shippings for the total weight is determined based on the range it falls into.<BR><BR>For instance, if total lbs for a customer order is 85lbs (this will be in the products table) and then on shipping table, a lookup into shipping table is made to find out which weight range 85 lbs falls into, the zip, and the shipping Charges for this.<BR><BR>This is then calculated and added to total customer purchase to get grand total.<BR><BR>I need a function that will do this calculation.<BR><BR>Can someone, please get me going on this, please?<BR><BR>Basically, we will pass in the shipping weight, the zipcode and an active connection to the function and the function will spit out the shipping charges.<BR><BR>Any help is greatly appreciated

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## You sure this isn't homework???

It sure FEELS like a school project. Very typical.<BR><BR>You are given ALL the information and just asked to solve one little aspect of the problem.<BR><BR>It&#039;s also a totally UNREALISTIC problem. Because NOBODY in the REAL business world would create a table that had the shipping charges for EVERY SINGLE ZIP CODE in it. But it&#039;s typical of the kind of problem assigned as homework in school.<BR><BR>SO...<BR><BR>We don&#039;t do homework. On purpose. We want students to *LEARN*, and students will never learn if we hand them the answers.<BR><BR>Now...<BR><BR>If you were to code a solution that *almost* worked and came and asked us about, perhaps, one or two lines of code in that solution... Yeah, we&#039;d be happy to answer.<BR><BR>This particular problem is *almost* a one line solution. Oh, you need a recordset, and you need to extract a value from that recordset. But the only important part of the solution is the SQL query. So see if you can come up with *THAT* first.<BR><BR>

3. Senior Member
Join Date
Dec 1969
Posts
708

## RE: Ha

It probably is too simple for you, Bill.<BR><BR>I have been done with school now since 1991.<BR><BR>I am still working on the zip code thing.<BR><BR>I have been trying to construct a query with case statement like this:<BR><BR>SELECT <BR> CASE Shippingcharge <BR> WHEN products.ShippingWeight BETWEEN 1 AND 15 THEN &#039;\$5&#039;<BR> WHEN products.ShippingWeight BETWEEN 16 AND 50 THEN &#039;\$15&#039;<BR> WHEN products.ShippingWeight BETWEEN 50 AND 100 THEN &#039;\$25&#039;<BR> WHEN products.ShippingWeight BETWEEN 100 AND 150 THEN &#039;\$35&#039;<BR>Else &#039;50&#039;<BR><BR>End<BR><BR>but my real problem is coming up with a function with this sql embedded.

4. Senior Member
Join Date
Dec 1969
Posts
708

## RE: You sure this isn't homework???

I have also tried this query:<BR><BR>SELECT s.ShippingCharges FROM products p, Shipping s WHERE p.shippingWeight BETWEEN s.fromWeight AND s.toWeight AND p.zip=s.zip AND p.StateCode=s.StateCode<BR><BR><BR>This is even a more accurate query but I need to put this in a function to calculate the shipping charges for me.

5. Senior Member
Join Date
Dec 1969
Posts
96,118

## Okay...so why...

...did you then talk about a table???<BR><BR>fromWeight toWeight ZipCode shippingCharges <BR>1 lb 5 lbs 35514 \$25.00 <BR>6lb 10lbs 02541 \$18.00, <BR>etc <BR><BR>???<BR><BR>If you are going to HARD CODE the prices like that, then why use SQL at all?? Easier to just code it in VBScript.<BR><BR>[And your CASE WHEN is using illegal syntax, anyway.]<BR><BR>Shouldn&#039;t your shipping table *REALLY* look something like this:<BR><BR>fromWeight toWeight Zone shippingCharges <BR>0 5 1 25.00 <BR>6 10 1 18.00<BR>etc <BR><BR>And then have a ZONEs table that relates zip codes to zones?<BR><BR>Zone LowZip HighZip<BR> 1 00100 10299<BR> 1 10700 20199<BR> 2 10300 10699<BR> 2 20200 27199<BR>etc.<BR><BR>???<BR><BR>And then you&#039;d do something like<BR><BR>SELECT shippingCharges <BR>FROM shippingTable, Zones<BR>WHERE shippingTable.Zone = Zones.Zone<BR>AND @weight BETWEEN fromWeight AND toWeight<BR>AND @zip BETWEEN lowZip AND highZip<BR><BR>See? Say the zip is 10931 and the weight is 7 pounds.<BR><BR>So the @zip BETWEEN finds that it is between 10700 and 20199, making it Zone = 1. And then you find that 7 pounds is between 6 and 10 and, for Zone 1 in the shippingTable, the charges are 25.00<BR><BR>Yes? Or have I missed entirely what you are after?<BR><BR>Yes, both @zip and @weight *could* come from another table (e.g., an invoice table?) if you also JOINed to that table.<BR><BR>

6. Senior Member
Join Date
Dec 1969
Posts
96,118

## ??? But that will get...

...the shipping charges for *EVERY* product in your PRODUCTS table. And, worse, it will require that you have EVERY SINGLE ZIP CODE you will ever ship to in your Shipping table.<BR><BR>That&#039;s pretty bad design, I think.<BR><BR>

7. Senior Member
Join Date
Dec 1969
Posts
708

## RE: ??? But that will get...

The zone method sounds good but according to my client his research indicates that UPS shipping indicates that shipping charges vary based on zip codes and weight of product.<BR><BR>So I wish I could come up with an easier way of doing this.<BR><BR>Right now, there over 42,000 zip codes in our shipping table.<BR>Worse still, each zip code will have 31 shipping charges based on 31 weight ranges; from 1 t 150 with an increment of 5 and anything over 150 has a fixed shipping charge.

8. Senior Member
Join Date
Dec 1969
Posts
708

## RE: Okay...Bill...

I think I got it.<BR><BR>Hmmm, this really makes sense.<BR><BR>First, how many zones do we have, 4?<BR><BR>This really makes sense, wow.<BR><BR>I still need that sql in a function, though.

9. Senior Member
Join Date
Dec 1969
Posts
96,118

## WOW!

WHY are you DOING this????<BR><BR>If you are going to ship via UPS, why not use UPS&#039;s online web service that lets you invoke *it* to ask for the shipping charges?<BR><BR>Once you create this table, you&#039;ll just have to update it ALL THE TIME because UPS&#039;s rates do *NOT* stay the same!<BR><BR>Plus you aren&#039;t even accounting for UPS "Next Day" and "3 Day" and "2 Day" and *probably* aren&#039;t accounting for box sizes! (If a large package doesn&#039;t weigh very much, UPS charges the same as if it weighed a LOT more. We shipped a package that contained a framed picture with lots of styrofoam padding. It weighed only 17 pounds, but we got charged the 30 pound rate.)<BR><BR>I have to say I think you (or your company or your client) are making a *HUGE* mistake!<BR><BR>***********<BR><BR>Yes, UPS charges vary by zip code. But the zip codes *ARE* put into ZONES. If you had the UPS Zone Chart, you *could* do as I suggested. But you seem to working hard to do things the hardest way.<BR><BR>

10. Senior Member
Join Date
Dec 1969
Posts
96,118

## I think UPS has 9 zones

You&#039;d have to get their Zone Chart for your area.<BR><BR>&#062; I still need that sql in a function, though.<BR><BR>I&#039;m lost. What kind of function??? A TSQL user-defined function? Surely you don&#039;t mean a VBScript function? Why?<BR><BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•