I have a query that returns a list of top performers based on
the number of modules they have accessed and passed. It shows how
many modules passed even if they have taken more than 1 attempt to
pass. I want the report to be able to show the number of first time
passes only. The code I am using is as follows:
SELECT distinct pps_principals.name AS principals_name,
COUNT(*) AS coursecount
FROM (PPS_SCOS JOIN PPS_TRANSCRIPTS ON PPS_SCOS.SCO_ID =
JOIN PPS_PRINCIPALS ON PPS_TRANSCRIPTS.PRINCIPAL_ID =
PPS_PRINCIPALS.PRINCIPAL_ID AND PPS_TRANSCRIPTS.STATUS like '[P]'
AND PPS_TRANSCRIPTS.TICKET not like 'l-%'
and pps_scos.name like 'MT%'
GROUP BY pps_principals.name
ORDER BY coursecount desc