Page Comparison

...

Instructor Users

Query:

No Format

select count(UNIQUE mp.kerberos_name) FROM SAKAI_REALM_RL_GR gr, SAKAI_USER_ID_MAP um, MOTHRA_PERSON@SAKAI_EXT mp
WHERE NOT exists (SELECT 1 FROM SAKAI_USER where user_id = gr.user_id) and
gr.USER_ID = um.USER_ID and um.EID = mp.MOTHRAID and mp.USER_TYPE IN ('F','M','P','C')

Notes: This query pulls users out of the realm table and joins them to specific USER_TYPE(s) in the mothra person table. The unique clause in the count is required as a single user may be in multiple site realms.

Staff Users

Query:

No Formatcode

SELECT
count	COUNT(UNIQUE mp.kerberos_name) 
FROM
	SAKAI_REALM_RL_GR gr, SAKAI_USER_ID_MAP um, MOTHRA_PERSON@SAKAI_EXT mp 
WHERE
	NOT existsEXISTS (SELECT
					1 
				FROM
					SAKAI_USER 
where 				WHERE
					user_id = gr.user_id 
	)
and	AND
	gr.USER_ID = um.USER_ID andAND
	um.EID = mp.MOTHRAID andAND
	mp.USER_TYPE IN ('S', 'M', 'R', 'C')

Notes: This query pulls users out of the realm table and joins them to specific USER_TYPE(s) relating to staff(Staff, Staff/Instructor, Staff/Student, and Staff/Student/Instructor) in the mothra person table. The unique clause in the count is required as a single user may be in multiple site realms.

Graduate Student Users

Query:

No Format

SELECT UNIQUE
    mp.kerberos_name,
    case when bl.level_code = 'UG' then 'Undergrad' else 'Graduate' end AS STUDENT_TYPE
FROM
    mothra_person@sakai_ext mp, banner_level@sakai_ext bl,
    SAKAI_REALM_RL_GR gr, SAKAI_USER_ID_MAP um
WHERE
    NOT exists (SELECT 1 FROM SAKAI_USER where user_id = gr.user_id) and
    mp.banner_pidm = bl.pidm AND
    mp.mothraid = um.EID AND
    mp.USER_TYPE IN ('U','R','P','C') AND
    um.USER_ID = gr.USER_ID and
    bl.term_code=(select max(term_code) from BANNER_LEVEL@SAKAI_EXT where pidm=mp.banner_pidm)

Notes: This query requires post processing. It generates a list of kerberos names and a code(UG stands for undergraduates, all other codes are grad students) The post processing totals each type and assigns it to its particular bucket. It also joins in the USER_TYPE for students(and as you can see there is potential for overlap.)
Issue: Previous to this query, we had a scenario where a student was in the banner level code as both a undergrad and a grad. The above query has been corrected for this issue. One last thing remains, is to verify that the assumption that the double counting is truly an issue.

Undergraduate Student Users
- Notes: See Graduate Student Users above
Total Unique Users
- Notes: See Unique Users query in General Statistics section

...

Versions Compared

Old Version 22

New Version 23

Key