Hi all,
What I need is a query that will show the total Users who have Completed each Topic/Question.
these are my tables:
answers
ansID, questionID, userName, AnsStatus, ans1, ans2, ans3
questions
qID, qNum, q1, q2, q3
Topics
topicID, topicTitle, topicType, case_stem, modID, notesToOwner, topicOwner, categories, DueDate, quesID, topicStatus
Note: modID i another table where I grab the module name that is on a form with a dropdown. I don't care about the module name for this solution.
Basically the way this works is that all students are presented with topics and questions. The only time I capture the student username is after they have submitted the topic and answered the questions. I am using Active Directory FBA login. I didn't want to enumerate hundreds of users who log on and and then add them to SQL Table ,so I came up with this system. Not great but it works.
So all students who log onto the application see all topics and questions.
This is what i have that grabs the number of completed answers by user:
SELECT uName,COUNT(anID) As "TotalCount" FROM tbl_answers
Where ansStatus = 'Completed'
GROUP BY uName
then I have this query that when user is selected from the above query I get this:
SELECT topicID, topicTitle, case_stem, topicType, qID, qNum, q1, q2, q3, questionID, anID, uName, an1, an2, an3
FROM topic_Unit, tbl_questions, tbl_answers
WHERE tbl_answers.questionID = tbl_questions.qID
AND tbl_answers.ansStatus = 'Completed'
AND topic_Unit.quID = tbl_questions.qID
AND tbl_answers.uName = @uName
This query also gives me the total topics that a user has remaining:
SELECT topicID, topicTitle, modID, dueDate, moduleName, quID
FROM topic_Unit, tbl_Modules
WHERE tbl_Modules.moduleID = topic_Unit.modID
AND topic_Unit.dueDate >='6/27/2012' AND topic_Unit.dueDate <= '7/24/2012'
and topic_Unit.quID not in (select questionID from tbl_answers where uName= @user)
AND topic_Unit.topicType = 'conf' AND topic_Unit.status = 'Available'
Order By topic_Unit.dueDate Asc
the above works great, but now I need to show
topicTitle: |
total users |
Status
I want to group by topicTitle (distinct) if possible to get all the topics by name.
Some title will be dupes.
thanks for the help!
Our community of experts have been thoroughly vetted for their expertise and industry experience.