Adhoc Custom Database Query - Adding 'course enrolled date'

Adhoc Custom Database Query - Adding 'course enrolled date'

by S G -
Number of replies: 1

I have been using a, SQL query to extract the essential user information that I need for offline billing purposes.

The query below gives most of the essential details, however, it does not give the date when the user has enrolled into a specific course.

A user may have registered on the portal on the 1st of January, he might enroll in course 1 on 15th of January and subsequently enroll in course 2 on 15th of February. I need to track the date of enrollment into the specific course.

In short, I just need to add the 'course enrolled date' to the query below. I have tried everything I could to the extent of my SQL skills (which are pathetic at best).

I would sincerely appreciate any help in modifying the query below.

-----------------------------------------------------------------------------------------------------------

SELECT

user2.id AS userid,

user2.username AS username,

user2.firstname AS Firstname,

user2.lastname AS Lastname,

user2.email AS Email,


(SELECT UID.data FROM prefix_user_info_field UIF, prefix_user_info_data UID WHERE UID.fieldid = UIF.id AND UIF.shortname = 'usercity' AND UID.userid = user2.id) as 'usercity',

(SELECT UID.data FROM prefix_user_info_field UIF, prefix_user_info_data UID WHERE UID.fieldid = UIF.id AND UIF.shortname = 'userstate' AND UID.userid = user2.id) as 'state',

(SELECT UID.data FROM prefix_user_info_field UIF, prefix_user_info_data UID WHERE UID.fieldid = UIF.id AND UIF.shortname = 'mobilephone' AND UID.userid = user2.id) as 'mobilephone',

course.fullname AS Course

FROM prefix_course AS course 

JOIN prefix_enrol AS en ON en.courseid = course.id

JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id

JOIN prefix_user AS user2 ON ue.userid = user2.id

Average of ratings: -
In reply to S G

Re: Adhoc Custom Database Query - Adding 'course enrolled date'

by S G -

The second SQL query below gives the actual enroll date per course in the last column. But it does not give the email ID and doesn't give the custom fields - State & City.

Currently I run both the queries, take them to a spreadsheet and do a Vlookup using the UserID in the first column of each report. It is quite inaccurate & error prone since the same user ID gets repeated several times and excel takes the first look-up.

Since I have to hand-over this report to a fairly junior colleague, I am trying to make this as simple & error-free as possible.


In short:

I just need to add the 'course enrolled date' from the last column of second query to the first query below.


Query 1

SELECT

user2.id AS userid,

user2.username AS username,

user2.firstname AS Firstname,

user2.lastname AS Lastname,

user2.email AS Email,


(SELECT UID.data FROM prefix_user_info_field UIF, prefix_user_info_data UID WHERE UID.fieldid = UIF.id AND UIF.shortname = 'usercity' AND UID.userid = user2.id) as 'usercity',

(SELECT UID.data FROM prefix_user_info_field UIF, prefix_user_info_data UID WHERE UID.fieldid = UIF.id AND UIF.shortname = 'userstate' AND UID.userid = user2.id) as 'state',

(SELECT UID.data FROM prefix_user_info_field UIF, prefix_user_info_data UID WHERE UID.fieldid = UIF.id AND UIF.shortname = 'mobilephone' AND UID.userid = user2.id) as 'mobilephone',

course.fullname AS Course

FROM prefix_course AS course 

JOIN prefix_enrol AS en ON en.courseid = course.id

JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id

JOIN prefix_user AS user2 ON ue.userid = user2.id

Query 2

SELECT 
u.id,
u.username,
u.lastname,
u.firstname,
c.fullname,

DATE_FORMAT(FROM_UNIXTIME(ue.timecreated), '%Y-%m-%d %H:%i') AS 'Timecreated' FROM prefix_user_enrolments ue LEFT JOIN prefix_enrol e ON (ue.enrolid = e.id) LEFT JOIN prefix_course c ON (e.courseid = c.id) LEFT JOIN prefix_user u ON (ue.userid = u.id)

Average of ratings: -