SELECT 	usr.login AS 'Login',
        usr.firstname AS 'First Name',
        usr.lastname AS 'Last Name',
        usr.email AS 'Email',
        IF (objt.title = 'Canadian Police Knowledge Network', orgus.title, objt.title) AS 'ORGANIZATION',#Use if you do not want to see the service specfic Org and only the Service Name Comment out Portal NAme and Department Name
        obj.title AS 'Course Title',
        DATE_FORMAT(event_archive.create_date, '%Y-%m-%d') AS 'Enrolled On',
        IF(marks.status=2, (Select DATE_FORMAT(FROM_UNIXTIME(mem.origin_ts), '%Y-%m-%d')),'') AS 'Date Completed',
        IF(marks.status = 0,'ENRL',(IF(marks.status=1,'INPO',(IF(marks.status=2,'COMP','ENRL'))))) AS 'Status'

FROM	rbac_ua
		INNER JOIN
	usr_data usr ON usr.usr_id = rbac_ua.usr_id
		INNER JOIN
	rbac_fa ON rbac_ua.rol_id = rbac_fa.rol_id
		INNER JOIN
	object_reference ref ON ref.ref_id = rbac_fa.parent
		INNER JOIN
	object_data obj ON ref.obj_id = obj.obj_id
		LEFT JOIN
	obj_members AS mem ON ref.obj_id = mem.obj_id and usr.usr_id = mem.usr_id
		INNER JOIN
	event_archive ON event_archive.obj_id = obj.obj_id and event_archive.usr_id = usr.usr_id
		LEFT JOIN
	ut_lp_marks marks ON ref.obj_id = marks.obj_id and usr.usr_id = marks.usr_id
		LEFT JOIN
			(SELECT object_reference.ref_id,rbac_ua.usr_id,object_data.obj_id,object_data.title
			FROM rbac_ua
			JOIN rbac_fa ON rbac_fa.rol_id = rbac_ua.rol_id
			JOIN object_reference ON rbac_fa.parent = object_reference.ref_id
			JOIN object_data ON object_data.obj_id = object_reference.obj_id
			JOIN object_data role ON role.obj_id = rbac_ua.rol_id
			WHERE object_data.type = 'orgu' AND object_reference.deleted IS NULL)
	AS orgus ON orgus.usr_id = usr.usr_id
		LEFT JOIN
			(SELECT rbac_ua.usr_id,object_data.obj_id,object_data.title,rbac_ua.rol_id
			FROM rbac_ua
			JOIN object_data ON object_data.obj_id = rbac_ua.rol_id
			WHERE object_data.type = 'role')
	AS crsmem ON crsmem.usr_id = usr.usr_id AND crsmem.title=CONCAT('il_crs_member_',ref.ref_id)
        LEFT JOIN
	object_data objt_id ON objt_id.obj_id = 9
		LEFT JOIN
	object_translation objt ON objt_id.obj_id = objt.obj_id and objt.lang_code = 'en'

Where  obj.type = 'crs'
		AND ref.deleted IS NULL
        AND crsmem.title IS NOT NULL
        AND rbac_ua.rol_id <> 4
		AND from_unixtime(event_archive.create_date) >= DATE_ADD(curdate(),INTERVAL -90 day) AND from_unixtime(event_archive.create_date) < curdate()

Group By usr.usr_id, obj.obj_id

ORDER By 'Enrolled On' DESC