The ORM Foundation

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:

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

The answer is no, no you should not use a case statement here. ;)

This is what I discovered about condensing state codes into a single state.

1. Ensure the state or country code exists in your list.
2. Standardize the width of the codes to a single width
3. If the state or country code is not in the list or null make is equal empty string "".
4. Do steps 1 through 3 for all codes in the interested list.
5. Make sure the final string is at least the minimum width else send exception.
6. Take the left x number of characters in your hierarchy and thats your code.

Doing joins and complex "or" and "and" statements you can easily get lost in the design process.  Better to condense things down using basic string manipulation.

Hmm.. after pondering your requirements, I wondered if the problem might be solved by remodeling your database to reflect the facts before trying to use SQL to extract the information that you need.

So I made a sample model that is attached to this post.
Does this help?

Ken 

Yes I did use the brown book steps initially.  But I was coming in mid stream so to speak and could not change the design requirements.  But in workers comp we have Jurisdiction State which always comes first.  Then we have the Claimant's State which is next, then we have the Claimant's employer's state, and finally we have the Workers Comp main Group Employer's state which owns the Workers Comp Account for that Employer.  Also known as the client's state at my company.

So trying to create a complex join operation on these tables to derive the top to bottom requirements was unnecessary to map out in orm.  It simply needed to be condesnsed down to empty strings if there was no state code, other wise simply take the first left two digits.  The only orm requirement is that all state codes must be exactly 2 digits.  And that's the final state. Once you have the state code you can then apply state fees which are standardized medicare or medicaid or other calculations on your state of interest for comparison purposes.

Just so you know. the website failed three times to deliver a message to you at the "simtest.net" domain.

"Reason: Failed to connect to the recipients mail server.  No DNS information was found for the 'simtest.net' domain."

One way to fix this is to amend the email address in your user profile.

Ken

Sorry about that.  I changed it marcnoon@userlevelup.com .  New domain. I'm fixing my profile now.

 Anyway... here is a quick example of what i was talking about.

Given 4 state priorities... 

1. Jurisdiction State
2. Claimant's State
3. Employer's State
4. Client State

So its possible to have a state list like:

1.  "AZWYNCSC"   <-- "AZ" is the state to utilize to find state fee.
2. "WYNCSC"        <-- "WY" is the state to utilize to find state fee.
3. "NCSC"              <-- "NC" is the state to utilize to find state fee.
4. "SC"                  <-- "SC" is the state to utilize to find state fee.

Or werid combos like so

1. "AZSC" <-- "AZ" is the state to utilize to find state fee.
2. "NC" <-- "NC" is the state to utilize to find state fee.
3. "WYSC" <-- "WY" is the state to utilize to find state fee.
4. WYNC"  <-- "WY" is the state to utilize to find state fee.
5. ""  <-- this is a case where no state was found... maybe a new client???

Anyways... I thought I would share this b/c this seems like its possible to solve the problem with sub types and super types being absorbed or not absorbed.  The reason this might be useful is b/c you don't have to track nulls, you just have to track what is left over.

Marc,

Maybe I misunderstand something in your post, but it looks as though your solution breaks the rules of set theory in that each value in a set must be "atomic".

So the permissible values in the set represented by an ORM object type may not be combinations of things.

For example if your object type is called "StateCode" then, assuming that you are talking about the USA, the permissible values are the two letter abbreviations of each State such as TX, AZ, MN, MI IN. IL...

Of course, what you propose is perfectly possible in C# or some other programming language - but that's not ORM.

Ken 

Thanks Ken,

I re-looked over your message and I realize that you are indeed correct.   This state code is being used more like an attribute and should not be used at the conceptual level.  In fact when you combine several attributes like this and then condense them down like I described in the break down of states it does become quite fuzzy.   The whole reason I even brought this up is because at the time I was trying to conceptualize a problem that was based on attributes where the data entry was inconsistent for state codes of varying order of importance.

I think if I were to redesign this system using ORM CSDP steps I would have arrived at a different model for tracking Medicare state fees for our claimants versus whole sale prices for a given period of time in order to arrive at differences between the two which are useful for our customers to compare.  The problem I have had is going from a conceptual schema to an actual useful or meaningful usage of the data in order to present it in the form of a report or reports as a type of savings comparison.  Its hard to generate a savings when you are missing a state or two or three, and you have a rating chart which contains Medicare minimum cost per state based on a rate multiplier to the whole sale cost. 

But I would say that the requirements were met by simply condensing down to a single state from the available information in order to derive a report. I believe you can deduct now what all the elementary facts are for my situation and postulate how you might draw out the fact types and populate.   Check for entity types in my case I would use a Medicare State Fee rates for each state, and a list of claimants and their whole sale prices in invoice line items and state codes, and the entities that have state code that are referenced by the claimant including the employer, our clients and our invoices and line items.   The only derivation is that I might say that certain states are more important that other states.   The final steps I'd say that my claimant identifier, employer identifier, and client identifier are all unique and all fields are mandatory except state codes and at least one state code is mandatory for determining my savings compared to Medicare and my whole sale price is mandatory.

 Since I'm only focused on the state fee calculation I would discount any subset, exclusion or subtype for this problem.  I did not offer in any other deviation from the norm here and so I will stick with the final stated facts.   So I believe I have enough information to remodel this problem using ORM now and choose your use of atomic values.