Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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
add comment

1 Answer

up vote 4 down vote accepted

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.

share|improve this answer
    
+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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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