>That is not correct. This is what the fines should be.
>

>overweight   Fine amount
>3500          $40.00
>2500          $22.50
>1500         $10.00
>500            $2.50
>

>

Did you want total fine?

If yes, then just sum and group by ID - George gave me solution with SUM and Group, but I removed it to show it here.

In other words,

Select        Truck.ID,
               sum(Case When Truck.Overweight > fine.MaxWeight
            Then (MaxWeight - MinWeight + 1) * Fineamount
                        Else (OverWeight - MinWeight + 1) * FineAmount
                        End) As TotalFine
From        #ttt As Fine
               Inner Join #Truck As Truck
                       On Truck.Overweight >= Fine.MinWeight
 group by Truck.ID

>>Here is George Mastros solution - I'm still working on mine:
>>
>>

>>Select        Truck.*,
>>               Case When Truck.Overweight > fine.MaxWeight
>>            Then (MaxWeight - MinWeight + 1) * Fineamount
>>                        Else (OverWeight - MinWeight + 1) * FineAmount
>>                        End As Fine
>>From        #ttt As Fine
>>               Inner Join #Truck As Truck
>>                       On Truck.Overweight >= Fine.MinWeight
>>

>>
>>Though now after seeing it I can only say - how come I didn't figure this out myself! :)
>>
>>
>>>How's this
>>>
>>>

>>>create table #truck (id int, overweight int);
>>>insert #truck values (1,3500);
>>>insert #truck values (1,2500);
>>>insert #truck values (2,1500);
>>>insert #truck values (2,500);
>>>insert #truck values (1,500);
>>>
>>>create table #ttt (minweight int, maxweight int, fineamount numeric(8,4));
>>>insert #ttt values (1,1000,.005);
>>>insert #ttt values (1001,2000,.01);
>>>insert #ttt values (2001,3000,.015);
>>>insert #ttt values (3001,4000,.02);
>>>insert #ttt values (4001,5000,.025);
>>>insert #ttt values (5001,999999999,.03);
>>>
>>>

>>>
>>>>>
>>>>>>Can you post complete rate table
>>>>>>
>>>>>>>I am using a Sybase database but I would think the sql syntax should be almost the same. I have a requirement that I need to calculate fine for overweight trucks. The way the customer wants to do this is to have 1000 pound groups and each group has a progressively higher rate. For example, if a truck is 3500 lbs overweight then the calculation should be:
>>>>>>>

>>>>>>>1000 * .005   = $5.00
>>>>>>>1000 * .01     = 10.00
>>>>>>>1000 * .015   = 15.00
>>>>>>>500   * .02    = 10.00
>>>>>>>for a total of 40.00 
>>>>>>>

>>>>>>>I tried to create a temp table to hold the rates but I I'm stumped on how to do a join. I was hoping for a SQL solution if it is possible.
>>>>>>>
>>>>>>>Thanks.