Sometimes normalizing can be done too far IMO;
Creating a denormalized table (perphaps with a SP to guarantee the derived values in case the table grows a lot)

>

ID, WeightLimit, FreightRate, FuelRate, Startrate, Weightpayed
1,     1,         12,          1.2,          0,         0
2,      50,        1,         0.1,          12,        1
3,     99999,    0.5,         0.05,        61,       50

should ease the calculation to be a better solution from maintainance/grokking POV ;-)
Other table structures and "access logic" could eliminate the Weightlimit column to make less unclean ;-)


>


>Hi,
>
>I need some help on how to calculate the freight charge on a package.
>
>I have a table like this :
>
>

ID, WeightLimit, FreightRate, FuelRate
>1,     1,         12,          1.2
>2,      50,        1,         0.1
>3,     99999,    0.5,         0.05

>
>If I have a package weighing 2 kgs, then the total freight is calculated as:
>
>1st kg = 12
>2nd kg = 1
>Total = 13
>
>or if I had a package weighing 52 kgs:
>1st kg = 12
>next 49 kgs = 49 * 1 = 49
>next 2kgs = 2 * 0.5 = 1
>Total = 62
>
>Is there a way to achieve this in SQL?