Re: Help with SQL statement
If your table had a id number for every single course taught, this would be easy (since trainer 1 taught the performace course twice and there's no way to tell the two apart except by gaps in consecutive dates)
cID TrainerID Course Date
1 1 Performance 2005-04-03
1 1 Performance 2005-04-04
1 1 Performance 2005-04-05
2 2 MBTI 2005-04-11
2 2 MBTI 2005-04-12
3 1 Performance 2005-04-20
3 1 Performance 2005-04-21
select cid, tid, course, min(cdate) as start, max(cdate) as stop from tmp group by 1,2,3 order by 2,3;
+------+------+-------------+------------+------------+
| cid | tid | course | start | stop |
+------+------+-------------+------------+------------+
| 1 | 1 | Performance | 2005-04-03 | 2005-04-05 |
| 3 | 1 | Performance | 2005-04-20 | 2005-04-21 |
| 2 | 2 | MBTI | 2005-04-11 | 2005-04-12 |
+------+------+-------------+------------+------------+
Its possible to get this number using SQL only, but its complicated and there might be an easier way? Basically, you need to order your table by trainer, course, and date, add an auto increment id number to the ordered table, and then use a self join to find differences between successive rows.
Using an SQL variable like @cid=1, you can increment it each time in the self join if the trainer or course changes or the date increases by more than 1. It should be something like this, but I can check the details if you want to try it... The union is needed to get the first row followed by all the rest.
@cid=1;
CREATE new_table as
SELECT tid, course, cdate, 1 as cid from ordered_details where id=1
UNION
SELECT next.tid, next.course, next.cdate,
if(next.cdate=last.cdate+1 and next.tid=last.tid and next.course=last.course), @cid, @cid:=@cid+1)
from ordered_details next, ordered_details last
where next.id=last.id+1;
and now that group by should work...
Good luck,
Chris