Hi did indeed - it was me who removed GROUP BY and SUM as I thought we wanted to get multiple records to show as it was in a sample


>It is, but George not always give you a direct solution. You should think a little about it :-)
>

>DECLARE @Truck TABLE (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)
>
>DECLARE @ttt table (minweight int, maxweight int, fineamount numeric(8,4))
>insert @ttt values (   1,     1000,0.005)
>insert @ttt values (1001,     2000,0.01)
>insert @ttt values (2001,     3000,0.015)
>insert @ttt values (3001,     4000,0.02)
>insert @ttt values (4001,     5000,0.025)
>insert @ttt values (5001,999999999,0.03)
>
>Select        Truck.*,
>               SUM(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
>GROUP BY Truck.id, Truck.overweight
>ORDER BY Truck.overweight DESC
>

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