I am attempting to find out our Student to Tutor Ratio, based on logins over time.
To achieve this I have borrowed mysql from the ad-hoc contributed reports.
The code I borrowed was this, but made some small adjustments:
Log Activity Reports
Detailed "VIEW" ACTION for each ROLE (TEACHER,NONE-EDITING TEACHER and STUDENT)
SELECT l.action, COUNT( l.userid ) AS counter , r.name FROM `prefix_log` AS l JOIN `prefix_role_assignments` AS ra ON l.userid = ra.userid JOIN `prefix_role` AS r ON ra.roleid = r.id WHERE (ra.roleid IN (3,4,5)) AND (l.action LIKE '%view%' ) GROUP BY roleid,l.action ORDER BY r.name,counter DESC
My Aim is to find out each roles Logins over a date range.
By comparing the tutor roles with student roles and calculating a ratio based on logins.
I altered the code to this, which provides the data I need (See the parts in bold for updated commands) :
SELECT l.action, COUNT( l.userid ) AS counter , r.name, l.time
FROM `prefix_log` AS l
JOIN `prefix_role_assignments` AS ra ON l.userid = ra.userid
JOIN `prefix_role` AS r ON ra.roleid = r.id
WHERE (ra.roleid IN (1,2,3,4)) AND (l.action LIKE '%login%' )
AND ((l.time >= UNIX_TIMESTAMP('2016-03-01 00:00:00')))
GROUP BY roleid,l.action
ORDER BY r.name,counter DESC
This outputs the below table:
##############
| action | counter | name |
|---|---|---|
| login | 2916 | Manager |
| login | 1419 | Non-editing teacher |
| login | 11758 | Teacher |
############
I then use Excel to total the login counter for all tutor roles, in this case it's: 16,093 Logins since the unix timestamp (date) specified: 2016-03-01 00:00:00
I then compare this with another report, containing student roleids.
This gives me student and tutor logins over the same time period and then I product a ratio of Student to Tutors in Excel.
The Student to Tutor Ratio for my example then calculates as: 7.18:1
Now What I actually want is to have a date range, so I can input From Date to a To Date.
I have attempted to modify the mysql several times to get a date range. I have used many different techniques and I'm really struggling to get the output I desire.
Can someone see how I might achieve this?, i.e. Adding a date range and providing the Date From, Date To in the table output of columns.
As soon as I can do this I have a fantastic metric for showing if we have the right number of tutors for our many thousands of students.
Many Thanks for ANY assistance
Steve - SHEilds Ltd