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!
