How to improve this query (using table variables or not?)

Question

I'm having a bit of problems with these tables...

TableCost
(
TableCostId int,
Cost numeric,
MonthsId int
)

and

Taxes
(
Percentt numeric,
MonthsId int
)

I have to calculate a new value by getting the biggest Taxes.MonthsId that do not exceed the TableCost.MonthsId (for each row).

For example, if I have the following registers:

TableCost:

(1,10,1),
(2,5,3),
(3,12,7),
(4,17,8),
(5,22,11)

Taxes:

(55,1),
(60,2),
(65,6),
(70,10),
(75,12)

To calculate the TaxedCost of the

first row of the TableCost -> 10*55
Second row -> 5*60
Third row ->12*65
Fourth row -> 17*65
Fifth row -> 22*70

The select must return the following fields

(Cost,TaxedCost)

I've tought of declaring a table variable... storage the Taxes values on it, runs a normal select on the TableCost table and looping each of its rows calculating the TaxedCost after the select.

Any ideas of improve this query?

Answer 1

You can do this with a correlated sub query.

SELECT Cost, 
       Cost * (SELECT TOP 1 Percentt 
               FROM   Taxes t 
               WHERE  t.MonthsId <= tc.MonthsId 
               ORDER  BY t.MonthsId DESC) AS TaxedCost 
FROM   TableCost tc 

You may well get a more efficient plan though if you simply fill in the gaps in your Taxes table so you can do a straight forward join on MonthsId. At least that should allow the optimiser to consider something other than nested loops.