The ID only is not enough :-)

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