Thomas,

thanks. It took me a while to grok what you are suggesting :) and I'm still not sure I've got it.

With the structure you gave, what would the query be like to make the calculation?

The more I think about it, the more I think I need to add in a field for the "level" which would make the CTE that Martina gave me work.

>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?