2

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
4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.