How do I return a count of all people who have completed five specific courses?

Hi All,

I have a query that returns everyone who has completed a variety of courses.  Five of these courses are part of a specific curriculum. I'm trying to return ONLY the people who have completed ALL five courses and then count them.

Have a great day!

James
LVL 2
etd_onlineguysAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

WayneATaylorCommented:
Can you post the query?
0
etd_onlineguysAuthor Commented:
Here's the query.  It returns a count of anyone who has completed any of the five courses grouped by Instructor.  I need it to only count the people who have completed ALL five courses.
SELECT dbo.Inst.Inst_No,dbo.Inst.Inst_FName, dbo.Inst.Inst_LName,
       SUM(CASE WHEN TBL_TMX_Attempt.AttndStatusFK=1 then 1 else 0 end) AS Completions
FROM  dbo.Org INNER JOIN
               dbo.tblEmpOrg ON dbo.Org.Org_PK = dbo.tblEmpOrg.EmpOrg_OrgFK RIGHT OUTER JOIN
               dbo.Inst INNER JOIN
               dbo.TBL_TMX_ActInst ON dbo.Inst.Inst_PK = dbo.TBL_TMX_ActInst.InstFK INNER JOIN
               dbo.TBL_TMX_Activity AS TBL_TMX_Activity_1 INNER JOIN
               dbo.TBL_TMX_Attempt AS TBL_TMX_Attempt_1 ON TBL_TMX_Activity_1.Activity_PK = TBL_TMX_Attempt_1.ActivityFK ON 
               dbo.TBL_TMX_ActInst.ActivityFK = TBL_TMX_Activity_1.Activity_PK RIGHT OUTER JOIN
               dbo.TBL_TMX_UserRequiredAct RIGHT OUTER JOIN
               dbo.TBL_TMX_Activity INNER JOIN
               dbo.TBL_TMX_Attempt INNER JOIN
               dbo.tblEmp ON dbo.TBL_TMX_Attempt.EmpFK = dbo.tblEmp.Emp_PK ON dbo.TBL_TMX_Activity.Activity_PK = dbo.TBL_TMX_Attempt.ActivityFK ON 
               dbo.TBL_TMX_UserRequiredAct.EmpFK = dbo.tblEmp.Emp_PK AND dbo.TBL_TMX_UserRequiredAct.ActivityFK = dbo.TBL_TMX_Activity.Activity_PK ON 
               TBL_TMX_Attempt_1.Attempt_PK = dbo.TBL_TMX_Attempt.LinkAttemptFK RIGHT OUTER JOIN
               dbo.tblEmpJob INNER JOIN
               dbo.Job ON dbo.tblEmpJob.EmpJob_JobFK = dbo.Job.Job_PK ON dbo.tblEmp.Emp_PK = dbo.tblEmpJob.EmpJob_EmpFK ON 
               dbo.tblEmpOrg.EmpOrg_EmpFK = dbo.tblEmp.Emp_PK
WHERE (dbo.tblEmp.Emp_Active = 1) AND (dbo.tblEmp.Emp_No is Not null) AND (dbo.TBL_TMX_Activity.Code IN (N'000074570', N'000074600', N'000074606', N'000074586', N'000074597')) AND 
               (dbo.tblEmpJob.EmpJob_PrmyInd = 1) AND (dbo.tblEmpOrg.EmpOrg_PrmyInd = 1) AND (TBL_TMX_Activity_1.EndDt BETWEEN '2013-01-01' AND '2013-12-31' )
GROUP BY Inst_No,dbo.Inst.Inst_FName, dbo.Inst.Inst_LName  

Open in new window

0
plusone3055Commented:
yes please post the query
but it sounds like just a sum(count)
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Dale FyeOwner, Developing Solutions LLCCommented:
What is the structure of the table that contains the employees and the courses they have taken?  Does that table indicate course dates and whether they passed the course or not?  Are instructors of any interest, if not, I'd leave them out of the mix.

Generally, with an EmployeeCourses table that contains EmpID, CourseID, CourseDate, CourseStatus, I would write something like

SELECT CoursesPassed.EmpID
FROM (
SELECT DISTINCT EmpID, CourseID
FROM EmployeeCourses
WHERE CourseID IN (1, 2, 3, 4, 5)
AND CourseStatus = 'Pass') as CoursesPassed
GROUP BY EmpID
HAVING Count(CoursesPassed.CourseID) = 5
0
etd_onlineguysAuthor Commented:
The TBL_TMX_ATTEMPT table has UserID, ActivityID, StartDate, EndDate, and CompletionStatus.  I need the instructors because I'm trying to see how many people each instructor has taught the curriculum to.  The instructors are pulled in from the TBL_TMX_ActInst and Inst tables.
0
etd_onlineguysAuthor Commented:
My result should look something like this:

InstFName  InstLname  Completions
John             Doe             7  (means 7 people completed all five courses with this inst).
Jane             Doe             2
Frank           Smith          4
0
Scott PletcherSenior DBACommented:
So the people have to have had the same instructor for all 5 courses?

If not, say they had 1 course each from 5 different instructors -- does each instructor get to count that as 1 or 0.2?
0
etd_onlineguysAuthor Commented:
Good question.  Each instructor would count it as .2.
0
etd_onlineguysAuthor Commented:
Anyone have any ideas?
0
Scott PletcherSenior DBACommented:
I'm having trouble working thru the table relationships, given the way the joins are written/structured.

What you need to do is first, in a separate subquery (easiest), just get the students who have passed all five courses.  Then join that to the other tables to get your totals.  I tried to write that up but the table relationships in the original query aren't clear to me.
0
Dale FyeOwner, Developing Solutions LLCCommented:
So, the base query, which identifies all of those individuals who took all five courses might look something like above.   You would then have to join that back to your EmployeeCourses table, and probably your instructors table

SELECT I.InstFName, I.InstLName, Sum(.2) as Completions
FROM (
             SELECT CoursesPassed.EmpID
             FROM (
             SELECT DISTINCT EmpID, CourseID
             FROM EmployeeCourses
             WHERE CourseID IN (1, 2, 3, 4, 5)
             AND CourseStatus = 'Pass') as CoursesPassed
             GROUP BY EmpID
             HAVING Count(CoursesPassed.CourseID) = 5
             ) as Passed5
INNER JOIN EmployeeCourses as EC
ON Passed5.EmpID = EC.EmpID
INNER JOIN Instructors as I
ON EC.InstructorID = I.InstructorID
WHERE EC.CourseID IN (1,2, 3,4,5)
GROUP BY I.InstFName, I.InstLName
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
etd_onlineguysAuthor Commented:
Thanks guys.  I'll try these out.
0
etd_onlineguysAuthor Commented:
Worked great!  Thanks.
0
Dale FyeOwner, Developing Solutions LLCCommented:
Glad I could help.
0
Scott PletcherSenior DBACommented:
I guess I didn't help :-).
0
Dale FyeOwner, Developing Solutions LLCCommented:
Scott,

LOL,

I gave up on the OP's original query for the same reason you did.  I just wrote my query based on how I might have constructed my tables.  Seems the OP was able to figure that out.
0
etd_onlineguysAuthor Commented:
Dale - yes, I was able to adapt your query to make it work.

Scott - Lol!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.