I have two tables. (MS SQL Server 2012)
Performance
PortfolioID PortfolioCode Date MarketValue
1 Port1 12/31/12 100000
2 Port2 12/31/12 200000
3 Port3 12/31/12 300000
BillingRates
RateID FromRate ToRate Rate
1 1 100000 1
2 100000.01 200000 0.99
3 2000000.01 300000 0.98
4 300000.01 400000 0.97
I want to run a query that has something like a CASE statement where I say if the MarketValue of a portfolio on a particular date is between the variousvalue ranges within the tiers on the rates table than its marketvalue is multiplied by its respective rate. (The rate column represents percentage rates)
For example
Port1 falls in the RateID 1 tier and is multiplied by 1 100,000 * 1% = 1000
Port2 falls in the RateID 2 tier and is multiplied by .99 200,000 * .99% = 1980
Port3 falls in the RateID 3 tier and is multiplied by .98 300,000 * .98% = 2940
I have about 100 of these 'cases' and was thinking of doing something like this
SELECT COALESCE(
CASE WHEN condition1 THEN calculation1 ELSE NULL END,
CASE WHEN condition2 THEN calculation2 ELSE NULL END,
etc...
)
But I can't figure out the logic or how to best join the two tables to achieve this.
You want to join them like this:
select p.*, p.MarketValue * br.rate
from Performance p left outer join
BillingRates br
on p.MarketValue between br.[from] and br.[to]
This is called a non-equijoin. The performance on such joins is generally worse than on equi-joins. Having an index on [from], [to]
will help.
Also, you shouldn't use SQL reserved words as column names. That is, "from" and "to" are inconvenient names.
If there might not be a match, then you might want:
select p.*, p.MarketValue * coalesce(br.rate, 1.0)
so the result is not NULL
.