Wow, thanks Omatic (Steve) for the GREAT SQL function to score which SOM attribute has the highest score. We have four foundations in one database, and figuring out donor-affinity has been challenging. For Annual Fund, mostly, because last gift/largest gift can be so skewed by random event or memorial donations. But even for Major Gifts--we did a massive wealth rating upload, and it was stunning to us how many new prospects we had to go in one by one to see which foundation should take the first shot at them. Plus, our last two capital campaigns have been joint campaigns, so by giving history alone, we had lots of tied scores. We wanted to factor in board membership, planned giving, volunteering, event attendance, hospital seen in--all sorts of things to try to get a better handle on donor-affinity by foundation.  

This SQL requires us to have four profiles that each have the same max amount. Score those first.  Then run this SQL to see which score has the highest number. Returns of 1, 2, 3, or 4 are for one of the foundations. 12 is a tie between the first two foundations. 123 among the first three. And so on. LOVE. IT.

You need to be able to find the ID of your attributes.

I'm kind of in a flutter thinking of all the time this will save. Insert HEART here.

SELECT ISNULL(CASE 
    WHEN (FS.F1 > FS.F2) AND (FS.F1 > FS.F3) AND (FS.F1 > FS.F4) AND (FS.F1 > FS.F5) THEN 1
    WHEN (FS.F2 > FS.F1) AND (FS.F2 > FS.F3) AND (FS.F2 > FS.F4) AND (FS.F2 > FS.F5) THEN 2
    WHEN (FS.F3 > FS.F1) AND (FS.F3 > FS.F2) AND (FS.F3 > FS.F4) AND (FS.F3 > FS.F5) THEN 3
    WHEN (FS.F4 > FS.F1) AND (FS.F4 > FS.F2) AND (FS.F4 > FS.F3) AND (FS.F4 > FS.F5) THEN 4
    WHEN (FS.F5 > FS.F1) AND (FS.F5 > FS.F2) AND (FS.F3 > FS.F4) AND (FS.F4 > FS.F5) THEN 5
    WHEN (FS.F1 = FS.F2) AND (FS.F1 = FS.F3) AND (FS.F1 = FS.F4) AND (FS.F1 = FS.F5) THEN 12345

    WHEN (FS.F1 = FS.F2) AND (FS.F1 = FS.F3) AND (FS.F1 = FS.F4) AND (FS.F1 > FS.F5) THEN 1234
    WHEN (FS.F1 = FS.F2) AND (FS.F1 = FS.F3) AND (FS.F1 = FS.F5) AND (FS.F1 > FS.F4) THEN 1235
    WHEN (FS.F1 = FS.F2) AND (FS.F1 = FS.F4) AND (FS.F1 = FS.F5) AND (FS.F1 > FS.F3) THEN 1245 
    WHEN (FS.F1 = FS.F3) AND (FS.F1 = FS.F4) AND (FS.F1 = FS.F5) AND (FS.F1 > FS.F2) THEN 1345 
    WHEN (FS.F2 = FS.F3) AND (FS.F2 = FS.F4) AND (FS.F2 = FS.F5) AND (FS.F2 > FS.F1) THEN 2345

    WHEN (FS.F1 = FS.F2) AND (FS.F1 = FS.F3) AND (FS.F1 > FS.F4) AND (FS.F1 > FS.F5) THEN 123
    WHEN (FS.F1 = FS.F2) AND (FS.F1 = FS.F4) AND (FS.F1 > FS.F3) AND (FS.F1 > FS.F5) THEN 124 
    WHEN (FS.F1 = FS.F2) AND (FS.F1 = FS.F5) AND (FS.F1 > FS.F3) AND (FS.F1 > FS.F4) THEN 125 
    WHEN (FS.F1 = FS.F3) AND (FS.F1 = FS.F4) AND (FS.F1 > FS.F2) AND (FS.F1 > FS.F5) THEN 134 
    WHEN (FS.F1 = FS.F3) AND (FS.F1 = FS.F5) AND (FS.F1 > FS.F1) AND (FS.F1 > FS