Pass Rate

[Guest]

I have a query that reports a list of all passed modules code below. I also have a list that is produced that shows a list of all the modules an individual has failed. I want to be able to compare the 2 queries and make a new report that reports the modules passed first time by comparing the two queries, so that failed modules that were passed the second time round are filtered out.

Passed Modules query:

SELECT DISTINCT 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 'P'
AND dbo.PPS_SCOS.NAME LIKE 'MT%'
AND PPS_TRANSCRIPTS.TICKET not like 'l-%'
ORDER BY dbo.pps_transcripts.date_created desc

Failed Modules Query:

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 dbo.pps_transcripts.date_created desc

[Dan_Bracuk]

If you need those two queries for something else anyway, do a query of queries that includes something like

where somefield not in (#ValueList(queryname.fieldname)#

[Guest]

Dan,

Thanks, any any of how I can code this? as I have never done a query of queries. I need the query to be able to look at the list of failed modules and compare it to the lsit of passed modules and if the failed module is also in the passed module list it will not count that pass as a first time pass, only reporting modules that have been passed the first time.

Thanks

[Dan_Bracuk]

google is your freind. search on "coldfusion query of queries"