>>>>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?
>>>
>>>It looks like you can get your result with recursive CTE. However, recursive CTE is a cursor in disguise.
>>
>>Thanks, any particular example you were thinking of that could get me started?
>
>I think my article on hierarchical CTE should be a good start as the problem is somewhat similar
>
>
CTE and hierarchical queriesthanks