Join? Calculate cost per shipment based on calculation (CASE) per total order & Min. Charge

Last Post 13 Aug 2009 12:30 PM by cyorka. 2 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
DareDelSol
New Member
New Member

--
19 Jan 2009 11:49 PM
How would I generate a query that does the following for shipping data. The shipping transaction table contains a record for each package shipped but only rates per package on a different scale. I need the consolidated rate, but have to break out "shipments" that contain 2 or more orders...

1. Get sum of weight shipped per customer per day.
2. Now, if the customer had two orders shipped the same day, calculate the rate to consolidate both...
a. Rate needs to have minimum fees (I am using CASE shown below)
b. Rate changes by 'Zone' and 'Weight' bracket (so, I have a bunch of CASEs within CASEs)
3. Finally, I need to list each order with charges, but divide the charges from #2 above between multiple orders to each order proportionate to each orders weight.

CASE
WHEN M.Zone = '2' THEN CASE
WHEN SUM(M.Weight) < 500 THEN CASE
WHEN round(.0973 * SUM(M.Weight),2) < 15 then 15
ELSE round(.0973 * SUM(M.Weight),2) END

Total Rate for Cust#306 was found to be $70.83, but I need to allocate that answer to two orders #1297 and 1290 since he got the 500# rate.

Final outcome needed:
Order# | Cust# | Customer |City | Weight | Rate
1297 | 306 | CUST A | San Jose | 325 | 40.89
1290 | 306 | CUST A | San Jose | 225 | 29.94
1365 | 197 | CUST B | Tucson | 110 | 25.00
1366 | 207 | CUST C | Phoenix | 190 | 28.88

I would imagine there is some kind of table join or subquery, but not sure how to do all that with logic that is going to be shared between the two?

Thanks for your brain power anyone!

DareDelSol
New Member
New Member

--
03 Mar 2009 11:28 PM
Still no ideas...?
cyorka
New Member
New Member

--
13 Aug 2009 12:30 PM
Did you ever figure out a solution to this?


Acceptable Use Policy
---