Frank,


the denormalized table allows you to get the correct row for the "last fitting increment" with a simple

where @curWeight>Weightpayed and @curWeight<=Weightlimit

which is faster than filtering and then looking for the sorted top 1. Also having precalculated the cost of the previous rows/weight steps into Startrate for that weight you only have to add (@curWeight-weightPayed)*Freightrate to Startrate, probably tweaked by biz rule definiton to something like int(@curWeight-startweight+0.5)*Freightrate to get a ceiling() like behaviour of calculating full kg if they are not already INTs.

your examples:
02kg: 12 + (2-1)*1
52kg: 61 + (52-50)*0.5

Yes, if maintaining the table and NOT fixing all 4 partially interdependent columns opens more potential maintance problems. Easy to get a handle on that IMO, as you could delegate writing out the denormalized table from a pure maintainance table via program if really needed - the SQL offered as solution looked for my taste too long to even try to figure out if adding another row/weight step into the table would still work without error - my first idea was also to create a cursor according to the weight records needed and sum that cursor, which would guarantee the SQL needing no fix if records were added to the 3-record table - but then I thought that would be carrying normalization too far for the use case, esp. as the denormalized version

Select  Startrate + (@curWeight-weightPayed)*Freightrate from denormalized where @curWeight>Weightpayed and @curWeight<=Weightlimit

should perform much better if this was needed very often.

Hope I did not veer off somewhere by reading too fast ;-)

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?