April 29, 2012 at 2:33 am
Hi All,
I have an online exam which inserts the user answers in to a table then works out how many correct answers they have got which is working fine, but what i want to do before returning the TotalCorrect i want to put that value in my ExamResult table along side the userID etc, last week i recieved alot of help with this but im just seeking a little more help to finish this off I get a red line under the word TotalCorrect in the very last insert statement can someone help me work this one out?
The total Correct is used in the first part and holds the value im trying to insert into the ExamResult table which has the following fields
UserID int
ExamTotal int
SELECT er.UserID,
SUM(CASE WHEN UserAnswer = Answer THEN 1
ELSE 0
END) AS TotalCorrect
FROM dbo.ExamAnswers AS er
INNER JOIN dbo.Correct_ExamAnswers AS ea ON er.QuestionNumber = ea.QuestionNumber
Where er.UserID = @user-id
group by er.UserID
Insert into ExamResult
(UserID, ExamTotal)Values(@UserID, TotalCorrect)
Thank you for your time
April 29, 2012 at 2:38 am
.Netter (4/29/2012)
Hi All,I have an online exam which inserts the user answers in to a table then works out how many correct answers they have got which is working fine, but what i want to do before returning the TotalCorrect i want to put that value in my ExamResult table along side the userID etc, last week i recieved alot of help with this but im just seeking a little more help to finish this off I get a red line under the word TotalCorrect in the very last insert statement can someone help me work this one out?
The total Correct is used in the first part and holds the value im trying to insert into the ExamResult table which has the following fields
UserID int
ExamTotal int
SELECT er.UserID,
SUM(CASE WHEN UserAnswer = Answer THEN 1
ELSE 0
END) AS TotalCorrect
FROM dbo.ExamAnswers AS er
INNER JOIN dbo.Correct_ExamAnswers AS ea ON er.QuestionNumber = ea.QuestionNumber
Where er.UserID = @user-id
group by er.UserID
Insert into ExamResult
(UserID, ExamTotal)Values(@UserID, TotalCorrect)
Thank you for your time
Presumably you want the results of the first query inserted into ExamResult?
Should be something like
;with Results as (SELECT er.UserID,
SUM(CASE WHEN UserAnswer = Answer THEN 1
ELSE 0
END) AS TotalCorrect
FROM dbo.ExamAnswers AS er
INNER JOIN dbo.Correct_ExamAnswers AS ea ON er.QuestionNumber = ea.QuestionNumber
Where er.UserID = @user-id
group by er.UserID)
Insert into ExamResult
(UserID, ExamTotal)
select UserId, TotalCorrect from Results
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 29, 2012 at 2:47 am
Hi Thanks for the snippert i was playing around and i think this could work aswell?
Declare @CorrectAnswers int;
set @CorrectAnswers = (
SELECT er.UserID,
SUM(CASE WHEN UserAnswer = Answer THEN 1
ELSE 0
END) AS TotalCorrect
FROM dbo.ExamAnswers AS er
INNER JOIN dbo.Correct_ExamAnswers AS ea ON er.QuestionNumber = ea.QuestionNumber
Where er.UserID = @user-id
group by er.UserID)
Insert into ExamResult
(UserID, ExamTotal)Values(@UserID, @CorrectAnswers)
???
April 29, 2012 at 3:15 am
Hi thanks the snippet i recently gave didnt work it was moaning about EXISTS but iv stuck with yours and added an additional line to cater for my needs, the finished exam
;with Results as (SELECT er.UserID,
SUM(CASE WHEN UserAnswer = Answer THEN 1
ELSE 0
END) AS TotalCorrect
FROM dbo.ExamAnswers AS er
INNER JOIN dbo.Correct_ExamAnswers AS ea ON er.QuestionNumber = ea.QuestionNumber
Where er.UserID = @user-id
group by er.UserID)
Insert into ExamResult
(UserID, ExamTotal)
select UserId, TotalCorrect from Results
Select ExamTotal from ExamResult where UserID = @user-id
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply