Solved

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

Posted on 2014-03-03
17
426 Views
Last Modified: 2014-03-06
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
0
Comment
Question by:etd_onlineguys
  • 8
  • 4
  • 3
  • +2
17 Comments
 
LVL 10

Expert Comment

by:WayneATaylor
ID: 39901592
Can you post the query?
0
 
LVL 2

Author Comment

by:etd_onlineguys
ID: 39901604
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
 
LVL 22

Expert Comment

by:plusone3055
ID: 39901610
yes please post the query
but it sounds like just a sum(count)
0
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39901627
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
 
LVL 2

Author Comment

by:etd_onlineguys
ID: 39901711
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
 
LVL 2

Author Comment

by:etd_onlineguys
ID: 39901730
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39901753
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
 
LVL 2

Author Comment

by:etd_onlineguys
ID: 39903121
Good question.  Each instructor would count it as .2.
0
 
LVL 2

Author Comment

by:etd_onlineguys
ID: 39906205
Anyone have any ideas?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39906465
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
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 39906538
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
 
LVL 2

Author Comment

by:etd_onlineguys
ID: 39906547
Thanks guys.  I'll try these out.
0
 
LVL 2

Author Closing Comment

by:etd_onlineguys
ID: 39910399
Worked great!  Thanks.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39910433
Glad I could help.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39910541
I guess I didn't help :-).
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39910591
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
 
LVL 2

Author Comment

by:etd_onlineguys
ID: 39910601
Dale - yes, I was able to adapt your query to make it work.

Scott - Lol!
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question