0 Replies Latest reply on Dec 15, 2006 7:44 AM by score1

    Calculating Percentages

    score1
      I am working with a training DB. I have a query written that tells me the first time percentage pass rate for people sitting training courses. See code below.

      <cfquery name="passedModules" datasource="breeze" >
      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 '#start#'
      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 '#start#'
      AND dbo.PPS_TRANSCRIPTS.STATUS LIKE 'F'
      AND dbo.PPS_SCOS.NAME LIKE 'MT%'
      AND PPS_TRANSCRIPTS.TICKET not like 'l-%' )
      ORDER BY pps_transcripts.date_created desc
      </cfquery>

      <cfquery name="failedModules" datasource="breeze">
      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 '#start#'
      AND dbo.PPS_TRANSCRIPTS.STATUS LIKE 'F'
      AND dbo.PPS_SCOS.NAME LIKE 'MT%'
      AND PPS_TRANSCRIPTS.TICKET not like 'l-%'
      ORDER BY 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>

      This query works fine.

      I have been asked to create a new query that does the following but haven't been able to get it to work. It needs to do the following:

      Multiply the passes by 2
      Multiply the fails by 2
      Subtract the fails from passes to give total
      The CFSET produces a percentage pass rate, take this and divide it by 100
      Add Total and percentage to give new score

      Hope this makes sense.

      Any help gratefully received.