# Thread: Calculate percentage match between tables

1. Registered User
Join Date
Oct 2008
Location
San Diego
Posts
2

## Unanswered: Calculate percentage match between tables

I’m working on a project that compares attributes from two tables and returns a percentage match. I’m comparing employee’s responses to a “questionnaire” to what essentially is an answer key. Technically we do not call this a test… go figure. There are many keys and many “questionnaires” that are in SQL server 2005. The first value is the employee login name, the second value identifies the key, and the next 10 values are their responses to the quiz. The Answer key is the key identifier and the 10 ‘expected’ answers.

Example:
SmithJ, A, 3,5,2,5,2,6,8,4,5,3
JonesS, A, 3,4,2,5,2,6,7,4,5,3
PhelpsK, A, 4,5,2,5,2,6,8,4,6,5

Returns
SmithJ 100%
JonesS 80%
PhelpsK 70%

Each value is atomic in its own field. I could have calculated the percentages when they were inserted into the database but I’ve inherited several hundred or thousands of stored results that I need to calculate.

2. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Is "3,5,2,5,2,6,8,4,5,3" the value held in one field, or is it like this "3", "5" , "2", ...

3. Registered User
Join Date
Oct 2008
Location
San Diego
Posts
2
Each value is in its own seperate field stored as an integer. They are stored in order in fields labled "Q1", "Q2", "Q3", etc.

4. Registered User
Join Date
Apr 2007
Posts
183
Code:
```DECLARE	@Answers TABLE
(
Nom VARCHAR(20),
Test CHAR(1),
A0 TINYINT,
A1 TINYINT,
A2 TINYINT,
A3 TINYINT,
A4 TINYINT,
A5 TINYINT,
A6 TINYINT,
A7 TINYINT,
A8 TINYINT,
A9 TINYINT
)

SELECT	'SmithJ', 'A', 3, 5, 2, 5, 2, 6, 8, 4, 5, 3 UNION ALL
SELECT	'JonesS', 'A', 3, 4, 2, 5, 2, 6, 7, 4, 5, 3 UNION ALL
SELECT	'PhelpK', 'A', 4, 5, 2, 5, 2, 6, 8, 4, 6, 5

DECLARE	@Questions TABLE
(
Test CHAR(1),
A0 TINYINT,
A1 TINYINT,
A2 TINYINT,
A3 TINYINT,
A4 TINYINT,
A5 TINYINT,
A6 TINYINT,
A7 TINYINT,
A8 TINYINT,
A9 TINYINT
)

INSERT	@Questions
SELECT	'A', 3, 5, 2, 5, 2, 6, 8, 4, 5, 3

SELECT		a.Nom,
(CASE WHEN a.A0 = q.A0 THEN 1 ELSE 0 END
+ CASE WHEN a.A1 = q.A1 THEN 1 ELSE 0 END
+ CASE WHEN a.A2 = q.A2 THEN 1 ELSE 0 END
+ CASE WHEN a.A3 = q.A3 THEN 1 ELSE 0 END
+ CASE WHEN a.A4 = q.A4 THEN 1 ELSE 0 END
+ CASE WHEN a.A5 = q.A5 THEN 1 ELSE 0 END
+ CASE WHEN a.A6 = q.A6 THEN 1 ELSE 0 END
+ CASE WHEN a.A7 = q.A7 THEN 1 ELSE 0 END
+ CASE WHEN a.A8 = q.A8 THEN 1 ELSE 0 END
+ CASE WHEN a.A9 = q.A9 THEN 1 ELSE 0 END) / 1E-1
INNER JOIN	@Questions AS q ON q.Test = a.Test```

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•