I have used this query on ms sql server in the past to get user hits per course
per month from the beginning of September.
select count(aa.pk1) as hits,u.user_id, cm.course_id,
dateadd( month, datediff( month, 0, aa.timestamp ), 0 ) as monthcount
from activity_accumulator aa
inner join course_main cm on aa.course_pk1=cm.pk1
inner join users u on aa.user_pk1=u.pk1
where aa.timestamp>='2008-09-01'
and user_id='user id'
group by user_id,course_id,dateadd( month, datediff( month, 0,
aa.timestamp ), 0 )
order by monthcount,course_id
The dateadd part resets the timestamp to the beginning of the month it
occurred in so you can then group by month.
I hope this helps!
Peter Langley
Swansea University.
|