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 |
|
aclarke
Posting Yak Master
133 Posts |
 Posted - 2001-10-16 : 19:01:31
|
| The shipping table in my e-commerce app has data from two shipping companies. Here's a partial description of that table: [ID] [int] IDENTITY (1, 1) NOT NULL , [shippingID] [int] NULL, [shippingCompanyID] [int] NULL , [destinationZipCode] [int] NULL , [directInterline] varchar(1) NULL, [minimumCharge] [money] NOT NULL , [L5C] [money] NULL , [5C] [money] NULL , [1M] [money] NULL , [2M] [money] NULL , [5M] [money] NULL , [10M] [money] NULL , [20M] [money] NULL , [30M] [money] NULL , [40M] [money] NULLSo. Given a weight and a destination zip code, I need to find the cheapest shipping company. Here are the complexities:- Each zip code does not have a corresponding entry. i.e. destinationZipCode covers that zip code up to one less than the next highest number for that shipping company. The two companies do NOT follow the same list of "reference" (or whatever you want to call them) zip codes.- The price is based upon weight breaks. So for weights of <500lbs, I use L5C. For weights between 10klbs-19,999lbs I use [10M], >=40klbs, [40M], etc. Then these numbers are multiplied by (weight/100). Don't ask...- The number calculated above is then multiplied by a discount factor pulled for each shipping company. These discount factors are different for each shipping company AND are different for different rows in the shipping table, depending on whether the carrier ships directly to this particular zip code or goes through another shipping company.- Once the shipping cost is calculated, if it's < minimumCharge, we use minimumCharge.- Of course then, we need to get the cost for each shipping company to a given zip code and weight, then find out which one is cheaper and return that shipping company, cost and destinationZipCodeI can think of several roundabout ways of doing this, but the best way I've come up with involves using a cursor, which makes me think there must be a Better Way. My current method involves getting a list of shippingCompanyIDs (there may be more than 2 in the future), then looping through each one and running a query against the shipping table for each one, using CASE to pull back the correct row. Then comparing the prices for each one a I go through the cursor loop. I guess the cursor only has to run twice, but still this is a pretty important query, guaranteed to be run a lot...I guess I could rebuild my shipping table to have 1 line for each shipping company and zip code but that would break other things (could be done though). |
|
|
|
|
|
|
|
Tricky tricky stored procedure.