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