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
>