I've searched for ways to do this but am not sure how to apply suggestions in my particular scenario, and I also don't know which approach would be the best or most accurate taking into account context of the data.

I am looking for the best way to select top performers from a table that contains scores for employees, given for the same measurements multiple times for a particular test. So an employee may be evaluated many times using the same test/measurements over time, and I am trying to select the top performers for a given period. In this example, the measurements are recorded in columns Question1 and Question2, and the employee is designated in the column EmployeeID. The scales of the scores are different by column, and by test, but all with a min of 0 or 1. In the sample data, test 1000 has columns Question1 (0-10) and Question2 (1-7), but you don't know the scales for sure based on the data, you only know that each column has a min of 0 or 1 and can be null meaning that value should be excluded from averaging calculations. I want to be able to say, give me the two top performers for test 1000 and order them accordingly based on that performance.

```
   ID            TestID        Question1       Question2       Question3       EmployeeID
   1              1000             10             null            null             12
   2              1000             10              7              null             12
   3              1000             10              7              null             12
   4              1000             10              7              null             12
   5              1000             10              7              null             12
   6              1000             10              7              null             5
   7              1000             10              7              null             5
   8              1000             10              7              null             5
   9              1000             10              7              null             5
   10             1000             10              7              null             5
   11             1000             10              7              null             5
   12             1000             10              7              null             5
   13             1000             10              7              null             5
   14             1000             10             null            null             5
   15             1000             10              7              null             5
   16             2000             5               10              10              3
   17             2000             5               0               3               3
   18             2000            null             9               8               6
   19             2000             5               10              9               6
   20             2000            null             10              9               7
   21             2000            null             10              9               7

```

In this example, for test 1000, if I get the top two performers, ranked, the result should be employee 5 and employee 12 ranked the same, because though they both have 10s and 7s and each include one null, which shouldn't be considered.

This SQL was suggested to me but it doesn't seem to work the way I want. The resulting SumAve ends up at 8.5789 for employee 5 and 8.6667 for employee 12, which doesn't seem right because they have the same averages of their scores that were recorded, and they shouldn't be penalized for having any nulls. But even if they were, that is the opposite of what I thought would be the case since employee 5 has more records (7s) I would think their average would be impacted less by a missing/low score.

```
SELECT TOP 2
  EmployeeID
 ,NoOfResults
 ,Question1
 ,Question2
 ,Question3
 ,SumAve
FROM (SELECT
    [EmployeeID]
   ,COUNT(*) AS NoOfResults
   ,AVG([Question1]) AS Question1
   ,AVG([Question2]) AS Question2
   ,AVG([Question3]) AS Question3
   ,SUM(ISNULL([Question1], 0) + ISNULL([Question2], 0) + ISNULL([Question3], 0)) / 
    SUM(
    CASE
      WHEN Question1 IS NULL THEN 0
      ELSE 1
    END +
    CASE
      WHEN Question2 IS NULL THEN 0
      ELSE 1
    END +
    CASE
      WHEN Question3 IS NULL THEN 0
      ELSE 1
    END) AS SumAve
  FROM Table1
  WHERE TestID = '1000'
  GROUP BY [EmployeeID] perform
ORDER BY SumAve DESC, EmployeeID ASC

```

Also, one last wrench to throw in, it's possible for a record to contain all null values for Question1, Question2 and Question3. Meaning the test was given but no scores were recorded (for whatever reason). In this case, again, the null values should be ignored leaving an employee with no rank or lowest rank / not included if that's their only record but using other records if they exist. 
 And I suppose it's possible for one employee to have multiple records but nulls in one column on all records, so I've added that as well. Let me know if that's the straw that broke the camel's back though because if that's the case I might be able to change something in the available data.

Thanks for looking at this, your help is much appreciated.

---

Update -

Using @scsimon's suggested method doesn't seem to give me what I'm looking for. It appears that the result is affected by nulls, which earlier I mentioned shouldn't be used to penalize the employee's overall score/ranking. In the following example, employees 10 and 5 both have highest scores (10s and 7s), but employee 5 has one score missing which should not affect their ranking. Employee 12 has scores that aren't perfect (one score of 9 instead of 10), which should reflect a lower ranking than employees 10 and 5, but as you can see employee 5 ends up lower, so they are being penalized for a missing score, which they should not. The ranking for test 1000 should have employees 10, 5, 11 and 13 at the top, with employee 12 below them.

*SQL for data*

```
DECLARE @table TABLE (
  ID INT IDENTITY(1,1) NOT NULL
 ,TestID INT
 ,Question1 INT
 ,Question2 INT
 ,Question3 INT
 ,EmployeeID INT
)
INSERT INTO @table
  VALUES 
  (1000, 10, 7, NULL, 10),
  (1000, 10, 7, NULL, 10),
  (1000, 10, 7, NULL, 10),
  (1000, 10, 7, NULL, 10),
  (1000, 10, 7, NULL, 10),
  (1000, 10, NULL, NULL, 11),
  (1000, 10, 7, NULL, 11),
  (1000, 10, 7, NULL, 11),
  (1000, 10, 7, NULL, 11),
  (1000, 10, 7, NULL, 11),
  (1000, 10, 7, NULL, 12),
  (1000, 9, 7, NULL, 12),
  (1000, 10, 7, NULL, 12),
  (1000, 10, 7, NULL, 12),
  (1000, 10, 7, NULL, 12),
  (1001, 10, 7, NULL, 12),
  (1001, NULL, 6, NULL, 12),
  (1000, 10, 7, NULL, 5),
  (1000, 10, 7, NULL, 5),
  (1000, 10, 7, NULL, 5),
  (1000, 10, 7, NULL, 5),
  (1000, 10, 7, NULL, 5),
  (1000, 10, 7, NULL, 5),
  (1000, 10, 7, NULL, 5),
  (1000, 10, 7, NULL, 5),
  (1000, 10, NULL, NULL, 5),
  (1000, 10, 7, NULL, 5),
  (1000, 10, 7, NULL, 13),
  (1000, 10, 7, NULL, 13)

```

*Data*

```
     ID       TestID   Question1  Question2  Question3  EmployeeID
     18        1000        10         7         null        5     
     19        1000        10         7         null        5     
     20        1000        10         7         null        5     
     21        1000        10         7         null        5     
     22        1000        10         7         null        5     
     23        1000        10         7         null        5     
     24        1000        10         7         null        5     
     25        1000        10         7         null        5     
     26        1000        10        null       null        5     
     27        1000        10         7         null        5     
     1         1000        10         7         null        10    
     2         1000        10         7         null        10    
     3         1000        10         7         null        10    
     4         1000        10         7         null        10    
     5         1000        10         7         null        10    
     6         1000        10        null       null        11    
     7         1000        10         7         null        11    
     8         1000        10         7         null        11    
     9         1000        10         7         null        11    
     10        1000        10         7         null        11    
     11        1000        10         7         null        12    
     12        1000        9          7         null        12    
     13        1000        10         7         null        12    
     14        1000        10         7         null        12    
     15        1000        10         7         null        12    
     16        1001        10         7         null        12    
     17        1001       null        6         null        12    
     28        1000        10         7         null        13    
     29        1000        10         7         null        13    

```

*Suggestion 1: SQL for result*

```
SELECT
  t.EmployeeID
 ,t.TestID
 ,SUM(ISNULL(Question1, 0) + ISNULL(Question2, 0) + ISNULL(Question3, 0)) / (COUNT(t.TestID) * 1.00) AS Ranking
FROM @table t
GROUP BY t.EmployeeID
        ,t.TestID
ORDER BY t.TestID,
Ranking DESC

```

*Suggestion 1: Result*

```
   EmployeeID        TestID          Ranking    
       10             1000            17.0      
       13             1000            17.0      
       12             1000            16.8      
        5             1000            16.3      
       11             1000            15.6      
       12             1001            11.5      

```

And with regard to the other suggestion, it doesn't appear to be outputting what I need either -

*Suggestion 2: SQL for result*

```
SELECT
  EmployeeID
 ,TestID
 ,SUM(ISNULL(Q1, 0) + ISNULL(Q2, 0) + ISNULL(Q3, 0)) / MIN(CASE
    WHEN Q1 IS NULL THEN 0.0
    ELSE 1.0
  END +
  CASE
    WHEN Q2 IS NULL THEN 0.0
    ELSE 1.0
  END +
  CASE
    WHEN Q3 IS NULL THEN 0.0
    ELSE 1.0
  END) AS AverageScore
FROM cte
GROUP BY EmployeeID
        ,TestID
ORDER BY TestID
, AverageScore  

```

*Suggestion 2: Result*

```
   EmployeeID        TestID       AverageScore  
        5             1000             8.5      
       10             1000             8.5      
       11             1000             8.5      
       12             1000             8.5      
       13             1000             8.5      
       12             1001             8.5      

```