Solved

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

Posted on 2014-03-03
17
435 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

 
LVL 48

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 48

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 48

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 48

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

688 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