3 Replies Latest reply on Nov 29, 2006 5:35 AM by Dan Bracuk

    Pass Rate

    score1
      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

        • 1. Re: Pass Rate
          Dan Bracuk Level 5
          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)#

          • 2. Re: Pass Rate
            score1 Level 1
            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
            • 3. Re: Pass Rate
              Dan Bracuk Level 5
              google is your freind. search on "coldfusion query of queries"