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>
3
Replies
AdChoices