7 Replies Latest reply on Feb 28, 2008 6:26 AM by Lemur99


      I’m trying to work out how long it takes a user to complete online training.

      The following query returns 271 rows form 4 different training courses, i want to take the maximum & minimum for each course and work out the difference.

      How can I achieve this?

      select pps_principals.name as UserName, pps_transcripts.status, pps_transcripts.score, pps_transcripts.max_score as MaxScore, pps_scos.name as ModuleName, pps_transcript_details.date_created as DateCreated
      from pps_principals, pps_transcripts, pps_scos, pps_transcript_details
      where pps_principals.principal_id = pps_transcripts.principal_id
      and pps_transcripts.sco_id = pps_scos.sco_id
      and pps_transcripts.transcript_id = pps_transcript_details.transcript_id
      and pps_principals.name = 'Lee Duncan'
      and pps_transcripts.status = 'F'
      and pps_scos.name like 'MTM-%'
      and pps_transcripts.ticket not like 'l-%'

        • 1. Re: Min/Max/Sum
          Level 7
          you want a max and min of exactly which columns?

          Azadi Saryev
          • 2. Min/Max/Sum
            Lemur99 Level 1
            pps_scos.name, that query returns four different training courses and the min and max for pps_transcript_details.date_created
            • 3. Re: Min/Max/Sum
              Level 7
              hmm... confused... so MAX and MIN of WHAT do you want from that query?
              as far as i can see, pps_scos.name is just a varchar field holding some
              sort of name of a course or something...

              which of the fields returned by your query will have the values you want
              to get MAX and MIN of?

              Azadi Saryev
              • 4. Re: Min/Max/Sum
                Lemur99 Level 1
                Ok, let me try explain myself better and explain the situation.

                Every time someone takes an online training course, made using PowerPoint, a date created field is input into the database, this is done every time they view a new slide.

                I want to be able to select a user, return all the courses they have failed and the time it took. I can’t find a start and end date, so the only way I can see of working this out is by taking the min and max date created (pps_transcript_details.date_created) for a distinct training course (pps_scos.name) and working out the difference.

                The above query returns all the data for all the course I have failed (4), but it returns 271 rows because it also returns all the interactions (slide views), I want to narrow it down to the first & last entry, min and max, but for each individual course.
                • 5. Re: Min/Max/Sum
                  Level 7
                  if you need just MIN and MAX value of
                  pps_transcript_details.date_created for each distinct course, you can do
                  something like:

                  SELECT ps.name as ModuleName, MIN(ptd.date_created) as MinDateCreated,
                  MAX(ptd.date_created) as MaxDateCreated
                  FROM pps_principals pp, pps_scos ps, pps_transcripts pt,
                  pps_transcript_details ptd
                  WHERE 1=1
                  AND pp.principal_id = pt.principal_id
                  AND pt.sco_id = ps.sco_id
                  AND pt.transcript_id = ptd.transcript_id
                  AND pp.name = 'Lee Duncan'
                  AND pt.status = 'F'
                  AND ps.name like 'MTM-%'
                  AND pt.ticket not like 'l-%'
                  GROUP BY ps.name

                  NOTE: the above is untested, but should work... :)

                  Azadi Saryev
                  • 6. Re: Min/Max/Sum
                    Dan Bracuk Level 5
                    Date functions are db specific, but here is the general idea.

                    select min(datediff(minute, Enddate, startdate)) min_duration,
                    max(datediff(minute, Enddate, startdate)) max_duration,
                    avg(datediff(minute, Enddate, startdate)) avg_duration,

                    from some_table join
                    (select theuser, min(datefield) startdate, max(datefield) enddate

                    from sometables
                    where they failed
                    group by theuser ) x on x.theuser = some_table.the_user
                    • 7. Re: Min/Max/Sum
                      Lemur99 Level 1
                      much appreciated!