| Administrator's Handbook |
| Edutizer manuals | Administrator handbook | A2 Manage | Various database scripts | Update examresults after question has been updated |
Update examresults after question has been updated
Task: recalculate exam results after one of thequestions in the exam test has been updated after the exam has ended.
DISCLAIMER: This script has been tested by MWX engineers. This script is provided without any warranty. Do back up you database before using this script.
NOTE: currently this script supports only questions with one correct answer (YV)
When this might become handy:
- teacher finds a mistake in the test and wants to correct exam results
- question_id's of questions that need to be recalculated
- training_id of the training of which exam results you are about to recalculate
question_id: in teacher application open the question. The location of the page will be somehting like
http://..../editquestionform.asp?question_id=1074
1074 - is the question_id
training_id: in teacher application click on the training (either active or archived). Location of the page will be something like http://........//TrainingView3.asp?id=829
829 - is the training_id.
The script itself:
NOTE: currently this script supports only questions with one correct answer (YV)
-- DISCLAIMER:
-- This script has been tested by MWX engineers.
-- This script is provided without any warranty.
-- Do back up you database before using this script.
USE EdutizerDECLARE @p_TrainingID int
DECLARE @p_QuestionID int
SET @p_TrainingID = <training_id>
SET @p_QuestionID = <question_id>
-- recalculate question points
UPDATE trq
SET points = a.points * q.max_points,
result = (a.points * q.max_points) / q.max_points
FROM
TestResults_Questions trq
JOIN Questions q ON q.question_id = trq.question_id
JOIN Testresults tr ON tr.testresult_id = trq.testresult_id
JOIN Examresults er ON er.examresult_id = tr.examresult_id
JOIN Answers a ON a.question_id = q.question_id
JOIN TestAnswers ta ON ta.answer_id = a.answer_id
WHERE
er.training_id = @p_TrainingID
AND q.question_id = @p_QuestionID
-- recalc testparts results
declare @part_results TABLE
(testresult_id int, testpart_id int, points real)
INSERT INTO @part_results
SELECT
trq.testresult_id,
trq.testpart_id,
SUM(trq.points)
FROM
TestResults_Questions trq
JOIN Testresults tr ON tr.testresult_id = trq.testresult_id
JOIN Examresults er ON er.examresult_id = tr.examresult_id
WHERE
er.training_id = @p_TrainingID
AND trq.question_id = @p_QuestionID
GROUP BY
trq.testresult_id, trq.testpart_id
UPDATE trt
SET
points = x.points,
result = x.points / trt.max_points
FROM
TestResults_TestParts trt
JOIN @part_results x ON x.testresult_id = trt.testresult_id and x.testpart_id = trt.testpart_id
UPDATE erp
SET
points = x.points
FROM
TestResults_TestParts trt
JOIN @part_results x ON x.testresult_id = trt.testresult_id and x.testpart_id = trt.testpart_id
JOIN Testresults tr ON tr.testresult_id = trt.testresult_id
JOIN ExamResults_Parts erp ON erp.examresult_id = tr.examresult_id AND part_no = trt.testpart_id
--recalc testresults
DECLARE @test_results TABLE (testresult_id int, points real)
INSERT INTO @test_results
SELECT testresult_id,
SUM(points)
FROM
@part_results
GROUP BY
testresult_id
DECLARE @trid int
DECLARE cur_tr CURSOR FOR
SELECT testresult_id FROM @test_results
FOR READ ONLY
OPEN cur_tr
FETCH cur_tr INTO @trid
WHILE @@FETCH_STATUS > 0
BEGIN
UPDATE Testresults
SET points = (SELECT points FROM @test_results WHERE testresult_id = @trid)
WHERE testresult_id = @trid
FETCH cur_tr INTO @trid
END
CLOSE cur_tr
DEALLOCATE cur_tr
|
||||||
| ||||||
|
||||||
