Results 1 to 4 of 4
  1. #1
    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

    Answer Key ‘A’= A, 3,5,2,5,2,6,8,4,5,3

    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. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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", ...
    Post your table DDL please
    George
    Home | Blog

  3. #3
    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. #4
    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
    	)
    
    INSERT	@Answers
    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
    FROM		@Answers AS a
    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
  •