This content has been marked as final. Show 7 replies
pps_scos.name, that query returns four different training courses and the min and max for pps_transcript_details.date_created
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?
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.
if you need just MIN and MAX value of
pps_transcript_details.date_created for each distinct course, you can do
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,
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... :)
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
where they failed
group by theuser ) x on x.theuser = some_table.the_user