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.
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!