Select a single row by using weighted column matches. More matches = more weight-CodePudding

Users in our system can earn points by performing certain "activities". The amount of points can differ depending on their country, their line of business, and their role. I'm having trouble obtaining a single value from the points table.

ID ActivityFK CountryFK BusinessTypeFK RoleFK Points
1  1          NULL      1              NULL   100
2  1          13        1              NULL   50
3  1          13        1              4      75
4  1          13        NULL           NULL   25
5  1          NULL      NULL           5      15

The idea is that I'd like to get the amount of points the person obtains by using the most restrictive match. (The more criterion they meet, the higher importance of the match)

Examples with expected results:

User A:
Country:13
Business:2
Role:4
Points:25

User A only matches the row with ID = 4. 25 points

User B:
Country:13
Business: 1
Role: 2
Points: 50

User B matches with 3 rows (ID = 1, 2, and 4). Row 2 has the most matches, so 50 points

User C:
Country: 13
Business: 1
Role: 4
Points: 75

User C matches with 4 rows (ID = 1, 2, 3, and 4). Row 3 has the most matches, so 75 points

User D:
Country: 5
Business: 1
Role: 5
Points: 100

User D matches 2 rows equally (ID = 1,5) so use the max point value available between them

Is there a query that will discern between these levels of "matching" so that I can pull a single row and get the correct number of points for that user?

Any help is appreciated!

CodePudding user response：

If you add NULL to anything, the result is NULL so this seems to work as intended. If anyone sees a chance for more efficiency, let me know. I borrowed from this: SQL - select row with most matching columns

DECLARE @a int, @c int, @b int, @r int
SET @a = 1
SET @c = 13
SET @b = 2
SET @r = 4

SELECT TOP 1 Points FROM (
    SELECT 
        Points,
        CASE WHEN CountryFK = @c THEN 1 WHEN CountryFK IS NULL THEN 0 ELSE NULL END  
        CASE WHEN BusinessFK = @b THEN 1 WHEN BusinessFK IS NULL THEN 0 ELSE NULL END  
        CASE WHEN RoleFK = @r THEN 1 WHEN RoleFK IS NULL THEN 0 ELSE NULL END AS Weights
    FROM @ActConfig WHERE ActivityFK = @a
) as j 
WHERE Weights IS NOT NULL
ORDER BY Weights DESC, Points DESC