0 Replies Latest reply on Nov 28, 2006 3:00 AM by score1

    First Time Passes

    score1
      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 = PPS_TRANSCRIPTS.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

      Any ideas gratefully received.