complex delivery rates

Question

I want to calculate delivery rates

delivery rate depends on two factors

• weight
• zone charges

Weight will always be rounded of it for example if it is 0.6 then 1.0 even if it is one gram above the current weight slab it will be rounded of to the next weight slab

I am currently using a table for weight slab which already rounded their weights to their weight slab

  ExternOrderNo  sum_order
    2001820978     515
    2001811192     1032
    2001809820     3080
    2001812854     2572
    2001819252     270
    2001825261     1557
    2001806232     1302
    2001807415     1048
    2001816131     554
    2001821502     558
    2001812838     558
    2001821185     2098
    2001812650     563
    2001807956     1080
    2001807613     830
    2001820690     65
 

HERE weights are in GRAMS Then i rounded of them using

WITH Weight_KG AS ( SELECT ROUND(sum_order/1000,2) AS Weight_KG FROM focal-lens-345713.Cointab.X_Order_Weight)

SELECT WKG.Weight_KG, TRUNC(WKG.Weight_KG) + CEIL(MOD(CAST(WKG.Weight_KG AS NUMERIC), 1.0) / 0.5) * 0.5 FROM Weight_KG AS WKG

this gave me

0.52        1.0
1.03        1.5
3.08        3.5
2.57        3.0
0.27        0.5
1.56        2.0
1.3         1.5
1.05        1.5
0.55        1.0
0.56        1.0 

there are two types of charges and shipments that is fixed charge which will be for products that have weight below 0.5KG and additional and for each extra 0.5KG there will be an additional charge for example Zone=a type of shipment=forward charges, weight= 1.4

since weight is 1.4 it will rounded to the next weight slab which is 1.5 so delivery rate=fwd_a_fixed + 2*fwd_a_additional

(2*fwd_a_additional cause of the two extra weight slab)

If weight is below 0.5KG then delivery rate=fwd_zone name_fixed

and second type of shipment is forward+rto chrages which also has additional charges for each extra 0.5KG

if the weight is below 0.5KG then delivery rate=fwd_zone name_fixed + rto_zone name_fixed

if weight is above 0.5 then there will additional charges for example

weight =2.0 zone=b then

delivery rate=fwd_b_fixed + 3*additional_weight + rto_b_fixed + 3*rto_b_additional

there are total 5 zones a,b,c,d,e where each zone has 4 rates

• forward fixed
• forward additional
• rto fixed
• rto additional

example

fwd_a_fixed=29.5 fwd_a_additional=23.6 rto_a_fixed=13.6 rto_a_additional=23.6

since there are so many parameters I got confused and wrote very huge code for exmaple

         WHEN CCI.Type_of_Shipment= "Forward charges" AND CCI.Zone="a" AND Weight_slab_X.XKG=1.0 THEN  ROUND(CCR.fwd_a_fixed+CCR.fwd_a_additional,2)
         WHEN CCI.Type_of_Shipment= "Forward charges" AND CCI.Zone="a" AND Weight_slab_X.XKG=1.5 THEN  ROUND(CCR.fwd_a_fixed+2*CCR.fwd_a_additional,2)
         WHEN CCI.Type_of_Shipment= "Forward charges" AND CCI.Zone="a" AND Weight_slab_X.XKG=2.0 THEN  ROUND(CCR.fwd_a_fixed+3*CCR.fwd_a_additional,2)
         WHEN CCI.Type_of_Shipment= "Forward charges" AND CCI.Zone="a" AND Weight_slab_X.XKG=2.5 THEN  ROUND(CCR.fwd_a_fixed+4*CCR.fwd_a_additional,2)
         WHEN CCI.Type_of_Shipment= "Forward charges" AND CCI.Zone="a" AND Weight_slab_X.XKG=3.0 THEN  ROUND(CCR.fwd_a_fixed+5*CCR.fwd_a_additional,2)
         WHEN CCI.Type_of_Shipment= "Forward charges" AND CCI.Zone="a" AND Weight_slab_X.XKG=3.5 THEN  ROUND(CCR.fwd_a_fixed+6*CCR.fwd_a_additional,2)
         WHEN CCI.Type_of_Shipment= "Forward charges" AND CCI.Zone="a" AND Weight_slab_X.XKG=4.0 THEN  ROUND(CCR.fwd_a_fixed+7*CCR.fwd_a_additional,2)
         WHEN CCI.Type_of_Shipment= "Forward charges" AND CCI.Zone="a" AND Weight_slab_X.XKG=4.5 THEN  ROUND(CCR.fwd_a_fixed+8*CCR.fwd_a_additional,2)

and this is just for zone a i really want to find a more effective method to do this