1 Reply Latest reply on Dec 4, 2006 1:51 PM by dempster

    Calculating percentages

    score1
      I am working with a DB that has over a 1000 users enroled in training modules. I need to be able to filter out the top performers based on module pass rate. This is easy when coding in one name but I cannot work out how to get it to scan all users and then report the top performers. I have attached the code that brings back an individual result when a name is entered but I need it to be able to do this for all users.

      <cfquery name="passedModules">
      SELECT DISTINCT dbo.PPS_SCOS.NAME, pps_transcripts.date_created, score, max_score, status
      FROM (dbo.PPS_SCOS JOIN dbo.PPS_TRANSCRIPTS ON dbo.PPS_SCOS.SCO_ID = dbo.PPS_TRANSCRIPTS.SCO_ID)
      JOIN dbo.PPS_PRINCIPALS ON dbo.PPS_TRANSCRIPTS.PRINCIPAL_ID = dbo.PPS_PRINCIPALS.PRINCIPAL_ID
      WHERE dbo.PPS_PRINCIPALS.NAME LIKE 'nigel cordiner'
      AND dbo.PPS_TRANSCRIPTS.STATUS LIKE 'P'
      AND dbo.PPS_SCOS.NAME LIKE 'MT%'
      AND PPS_TRANSCRIPTS.TICKET not like 'l-%'
      AND dbo.PPS_SCOS.NAME NOT IN (
      SELECT DISTINCT dbo.PPS_SCOS.NAME FROM (dbo.PPS_SCOS JOIN dbo.PPS_TRANSCRIPTS ON dbo.PPS_SCOS.SCO_ID = dbo.PPS_TRANSCRIPTS.SCO_ID)
      JOIN dbo.PPS_PRINCIPALS ON dbo.PPS_TRANSCRIPTS.PRINCIPAL_ID = dbo.PPS_PRINCIPALS.PRINCIPAL_ID
      WHERE dbo.PPS_PRINCIPALS.NAME LIKE 'nigel cordiner'
      AND dbo.PPS_TRANSCRIPTS.STATUS LIKE 'F'
      AND dbo.PPS_SCOS.NAME LIKE 'MT%'
      AND PPS_TRANSCRIPTS.TICKET not like 'l-%' )
      ORDER BY dbo.pps_transcripts.date_created desc
      </cfquery>

      <cfquery name="failedModules">
      SELECT dbo.PPS_SCOS.NAME, pps_transcripts.date_created, score, max_score
      FROM (dbo.PPS_SCOS JOIN dbo.PPS_TRANSCRIPTS ON dbo.PPS_SCOS.SCO_ID = dbo.PPS_TRANSCRIPTS.SCO_ID)
      JOIN dbo.PPS_PRINCIPALS ON dbo.PPS_TRANSCRIPTS.PRINCIPAL_ID = dbo.PPS_PRINCIPALS.PRINCIPAL_ID
      WHERE dbo.PPS_PRINCIPALS.NAME LIKE 'nigel cordiner'
      AND dbo.PPS_TRANSCRIPTS.STATUS LIKE 'F'
      AND dbo.PPS_SCOS.NAME LIKE 'MT%'
      AND PPS_TRANSCRIPTS.TICKET not like 'l-%'
      ORDER BY dbo.pps_transcripts.date_created desc
      </cfquery>

      <cfset passes = passedmodules.recordcount>
      <cfset fails = failedmodules.recordcount>
      <cfset total = passes + fails>
      <cfif total GT 0>
      <cfset percentage = passes / total * 100>
      <cfelse>
      <cfset percentage="0">
      </cfif>

      <cfoutput>
      <div align="left" class="navigation_body">Module Pass Rate : <span class="red_bold">#numberformat(percentage,'99')#%</span></div>
      </div>
      </cfoutput><span class="navigation_body"></span><span class="navigation_body"></td>

        • 1. Re: Calculating percentages
          dempster Level 1
          Instead of restricting to one name, query for all your names and calculate the pass rate for each person by looping through your query output (grouped on user). Add this information to an array, then sort the array on the pass rate and display the top performers. Or else write the pass rate back to the person's record and then query again and sort by pass rate.