3 Replies Latest reply on Jan 24, 2007 12:15 AM by Lemur99

    Arrays or Variable

    Lemur99
      Hi,

      I’m trying to get a pass rating from my database, this information is not in the database but it’s calculated from information in the database.

      I have 1500 user enrolled in my training and each individual is in pps_principals.name

      What I want is the top 10 pass rating. My thoughts on with would be-

      1. Query pulling all the names from the database and saving them as a variable/array?
      2. Query using the variable/array to count each persons passes and saving them as variables/arrays?
      3. Query using the variable/array to count each persons fails and saving them as variables/arrays?
      4. Do the calculations for the pass rate then display the top 10

      Will this work and how?

      Here’s what I have for 1 user, which gives me a pass rating.

      <cfparam name="start" default=""> <!---which is defined from pervious page--->

      <cfquery name="passedModules" datasource="db”>
      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="db">
      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>

      <cfquery name="completedModules" datasource="db">
      SELECT DISTINCT dbo.PPS_SCOS.NAME, pps_transcripts.date_created
      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 'C'
      AND dbo.PPS_SCOS.NAME LIKE 'MT%'
      AND PPS_TRANSCRIPTS.TICKET not like 'l-%'
      ORDER BY pps_transcripts.date_created desc
      </cfquery>

      <cfquery name="completedModules" datasource="db">
      SELECT SUM(SCORE) AS TOTSCORE
      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_SCOS.NAME LIKE 'MT%'
      and PPS_TRANSCRIPTS.TICKET not like 'l-%'
      and pps_transcripts.max_score > 0
      and status like '[PF]'
      </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>


      <cfset pass = passedmodules.recordcount * 2>
      <cfset fail = failedmodules.recordcount * 2>
      <cfif total GT 0>
      <cfset percent = passes / total>
      <cfset total = pass - fail + percent>
      <cfelse>
      <cfset percent="0">
      </cfif>
        • 1. Re: Arrays or Variable
          mvierow Level 1
          You are going to want to get all of that logic into a single query if you want to pull the top 10 from 1500. Otherwise you are going have to pull all of the records, make all of the calculations, sort them, then display. Doing this in real-time on a page loaded by an end user would be punishment, both for them and for anyone else who may share your installation of CF.

          Unfortunately trying to reverse engineer your database based on these queries would take a little to much time and would likely be prone to error. If you would like a solid response, I would recommend creating a database diagram if you haven't already, take a screen capture, then send us a link to the image.
          • 2. Re: Arrays or Variable
            Dan Bracuk Level 5
            In addition to mvierow's comments, getting the top x records can be done on most dbs, but the syntax is db specific. What are you using?
            • 3. Re: Arrays or Variable
              Lemur99 Level 1
              SQL 2000 & ColdFusion to pull information from a breeze database

              I'll post a diagram some time later today

              Regards,
              Lee