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

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?

-
Given the data you've specified, you're going to have to run a correlated subquery. Performance is going to suck no matter what you do. – Simon Righarts Feb 7 '12 at 10:08

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.

-
+1 - Just a question out of curiosity, could this be done with an `OUTER APPLY` instead?, would it make any difference?. – Lamak Feb 7 '12 at 13:20
@Lamak - Yes. AFAIK `OUTER APPLY` would likely give the same plan though in this case. – Martin Smith Feb 7 '12 at 13:53
I figured it would be similar, thanks – Lamak Feb 7 '12 at 13:55