Hi all,
I have a datatable with results of a survey. As a result, i want to create an output for each user with the greatest and the least accordance.
For Example, this is my datatable: (QuestionID and UserID are UniqueIdentifiers while Answer is Integer)
+------------+--------+--------+
| QuestionID | UserID | Answer |
+------------+--------+--------+
| a | 1 | 1 |
| a | 2 | 1 |
| a | 3 | 2 |
| a | 4 | 3 |
| b | 1 | 2 |
| b | 2 | 1 |
| b | 3 | 2 |
| b | 4 | 1 |
| c | 1 | 3 |
| c | 2 | 3 |
| c | 3 | 2 |
+------------+--------+--------+
Now I want to create a stored procedure, using a UserID as input parameter. In this example, I'm using the first user '1' (UniqueIdentifier in live table) as input parameter. The SP should now compute a list with the top 3 users with the greatest accordance (so in the survey the answered most questions like user '1') and the top 3 users with the least accordance (so in the survey they answered most questions not like user '1').
Regarding the accordance, there could be only 'true' (same answer) or 'false' (not same answer). There is no relation in the answers like "3 is a higher miss than 2 if the user answered 1". So if the user answers "1", only "1" is correct accordance while "2" and "3" are both mismatches.
It's also possible that users did not answer all questions, e.g. see in above table that user 4 did not answer last question c. This will count as mismatch. So if user 1 answered 10 question and user 2 answered only 5 questions, he could not get more than 50% accordance even if he answered all 5 questions same as user 1. (This would be a Bonus later on: Only consider the users which answered at least 50% of all questions which are also answered by user 1. If so, missing answered will not count as mismatch but just be ignored. So if user 2 answered 6 questions and all with the same answer as user 1, he would have 100% accordance. But I'm already struggling with the basic part, so that's out of scope)
Of course the live table contains more than just 4 users so Top 3 would be best to return. The return table should than look like this: (Accordance has the number of same question answers and percentage is Accordance divided by number of questions answered by user 1 (in current example: 3))
+--------+------------+------------+
| UserID | Accordance | Percentage |
+--------+------------+------------+
| 2 | 2 | 66.67 |
| 3 | 1 | 33.33 |
| 4 | 0 | 0 |
+--------+------------+------------+
Any ideas how to do this? I've tried with several GROUP BY but was not successfull 
I've created this little script which shows the datatable and the expected result. Of course we have dozens of questions and hundreds of users, but I kept it simple in this example:
