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.MinWeightThough now after seeing it I can only say - how come I didn't figure this out myself! :)
>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); > >>
>>>>>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.