Extract category based on sum in range (without hardcoding CASE...WHEN) 1

[Katerine]

I have what I hope has a semi-simple answer.

Running SQL Server 2008 R2.

Let's say that I'm using a database, and need to find out a person's classification, based on how much money they've contributed. Is there a way to do this without hard-coding? The classifications are subject to change, so it would be nice if I could just grab the classification from a table.

What I really want to do, is total all of a contributor's contributions to date, and then return whether their classification, based on the following table:

Classification,Min,Max
'Non Member',0,49.99
'Professional',50,99.99
'Associate',100,249.99
'Principal',250,499.99
'President',500,999.99
'Chairman',1000,10000000

So I want to run a query that basically can grab the following info (based on the sum of their contributions, which I know how to get, and their place in the above table, which I don't know how to do):

ContributorID,SumOfAmount,Classification
15,$29.00,'Non Member'
28,$467.00,'Principal'

Is there any way to do this with a table lookup, or do I have to resort to hardcoding a CASE...WHEN statement?

Many thanks!

Katie

 

[gmmastros]

You can join the 2 tables. Something like this...

Select *
From   Contributors_Table
       Inner Join Classification_Table
          On Contributors_Table.SumOfAmount 
               Between Classification_Table.Min 
                   And Classification_Table.Max

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Katerine]

Many thanks - that did the trick!

(didn't realize that Between worked in the FROM clause... thought it was just for WHERE and HAVING. Learn something new...)

Katie

 

[gmmastros]

You could also have had multiple conditions like this:

Select *
From   Contributors_Table
       Inner Join Classification_Table
          On  Contributors_Table.SumOfAmount >= Classification_Table.Min 
          And Contributors_Table.SumOfAmount <= Classification_Table.Max

In this very specific situation, I think between is a little clearer. There will be absolutely no performance difference between this code and the one I posted previous, so the only real difference is how the code appears to developers. What I mean is... SQL Server will expand the between condition (during query execution) to be the same as this code block so that the execution plans are identical, therefore no difference in performance.

BTW, there are all sorts of things you can put in the ON clause.





-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom