Hi, I've been trying to put joins into a case statement in SQL. The current version of SQL code has joins between a state rates table and several other tables that have states in them. But since there is an agreed upon order to how these rates are applied applied depending on which state a table is from, I have been having trouble moving this to a case statement.
My questions are there situation that are more suited for joins than to case statements in SQL Server? And which one is more efficient? (Details below)
Here is a banking example on which fee should be applied:
State of Residence, Banking State, Insurance State, License State, Highschool state
each of these states are in a separate table, and there is a fee table that has each state listed with its associated fee.
So I have matrix:
Case # |
Residense State Length=2 |
Banking State Length=2 |
Insurance State Length=2 |
Drivers License State Length=2 |
Highschool State Length=2 |
|
|
|
|
|
|
|
|
|
1 |
TRUE |
n/a |
n/a |
n/a |
n/a |
or |
|
|
|
|
|
|
|
|
2 |
FALSE |
TRUE |
n/a |
n/a |
n/a |
or |
|
|
|
|
|
|
|
|
3 |
FALSE |
FALSE |
TRUE |
n/a |
n/a |
or |
|
|
|
|
|
|
|
|
4 |
FALSE |
FALSE |
FALSE |
TRUE |
n/a |
or |
|
|
|
|
|
|
|
|
5 |
FALSE |
FALSE |
FALSE |
FALSE |
TRUE |
So my join would look have to match each case. I'm using empty string '' for situation where that information was not provided and NULL are never allowed in this model. The constraint is that there can only be either two characters A-Z and must be listed in StateList table, or an empty string. Since each of the states above are in a separate table, I need to choose the rate from the above matrix. Where each item is not n/a it must be a binary AND with each state.
STATE PENALTY TABLE looks like this:
STATE PENALTY ASSESSED
AK $12.95
AL $13.95
...
There is also a state called NA which stands for Not Applicable with a penalty of $1.00.
In the above case if all states are empty string, then a penalty of $1.00 is assessed.
select sp.State, sp.Penalty Assessed from Residence as r inner join Banking as b inner join Insurance as i inner join DriversLicense as d inner join HighSchool as h inner join StatePenalty as sp
on r.State = sp.State -- Case 1
or r.State = '' and b.State = sp.State -- Case 2
or r.State = '' and b.State = '' and I.State = sp.State -- Case 3
or r.State = '' and b.State = '' and I.State = '' and d.Sate = sp.State -- case 4
or r.State = '' and b.State = '' and I.State = '' and d.State = '' and h.State = sp.State - case 5
or r.State = '' and b.State = '' and I.State = '' and d.State = '' and h.State = '' and sp.State = NA - case 5
Thanks,
Marc