Active Staff & Student Report

This is used in the Report Server (https://rps01.its.hawaii.edu) to supply the comprehensive list of active staff and students in ITS.

Access to Report Server:  Keith HIrata

SQL:

--Find all ACTIVE staff & students
--ALL STAFF
select sect.sectname as section, role.roledescr, pers.perslastname as lastname, pers.persfirstname as firstname, 
pers.persuhuuid as uhnumber, pers.persitsusername as username, 
pers.persemail as email, ppos.pposmanager as supervisor
from personnel pers inner join persposition ppos on pers.persid = ppos.persid
inner join section sect on ppos.sectid = sect.sectid
inner join role on pers.roleid = role.roleid
where ppos.pposstatus = 'A'
  and role.roleid !=9
  and ppos.ppostype = 1

UNION ALL
--ALL STUDENTS
select sect.sectname as section, role.roledescr, STDLINK.stdlastname as lastname, STDLINK.stdfirstname as firstname, 
pers.persuhuuid as uhnumber, pers.persitsusername as username,
pers.persemail as email, CONLINK.contactfirstname || ' ' || CONLINK.contactlastname as supervisor
from personnel pers inner join persposition ppos on pers.persid = ppos.persid
inner join section sect on ppos.sectid = sect.sectid
inner join role on pers.roleid = role.roleid
inner join SECEADM.student@secedb_link STDLINK on pers.persuhuuid = STDLINK.stduhuuid
inner join SECEADM.placement@secedb_link PLCLINK on STDLINK.stdId = PLCLINK.stdId
inner join SECEADM.jobcontacts@secedb_link SUPLINK on PLCLINK.plcjobid = SUPLINK.jobid
inner join SECEADM.contact@secedb_link CONLINK on SUPLINK.contactid = CONLINK.contactid  
where ppos.pposstatus = 'A'
  and role.roleid = 9
and PLCLINK.plcId = ppos.empId 
and SUPLINK.aprid = 2
order by section, roledescr, lastname, firstname

;

Old SQL:

--Find all ACTIVE staff & students
-- active staff
select sect.sectname as section, role.roledescr, pers.perslastname as lastname, pers.persfirstname as firstname,
pers.persuhuuid as uhnumber, pers.persitsusername as username, pers.persemail as email, ppos.pposmanager as supervisor
from personnel pers, persposition ppos, section sect, role
where pers.persid = ppos.persid
and pers.roleid != 9
and ppos.pposstatus = 'A'
and pers.roleid = role.roleid
and ppos.sectid = sect.sectid
UNION ALL
-- active students
select sect.sectname as section, role.roledescr, STDLINK.stdlastname as lastname, STDLINK.stdfirstname as firstname,
pers.persuhuuid as uhnumber, pers.persitsusername as username, pers.persemail as email, CONLINK.contactfirstname || ' ' || CONLINK.contactlastname as supervisor
from personnel pers, persposition ppos, section sect, role,
SECEADM.student@secedb_link STDLINK,
SECEADM.placement@secedb_link PLCLINK,
SECEADM.jobcontacts@secedb_link SUPLINK,
SECEADM.contact@secedb_link CONLINK
where pers.persid = ppos.persid
and pers.roleid = 9
and pers.persuhuuid = STDLINK.stduhuuid
and ppos.pposstatus = 'A'
and pers.roleid = role.roleid
and PLCLINK.plcStatus = 'A'
and PLCLINK.stdId = STDLINK.stdId
and PLCLINK.plcId = ppos.empId
and PLCLINK.plcjobid = SUPLINK.jobid
and SUPLINK.aprid = 2
and SUPLINK.contactid = CONLINK.contactid
and ppos.sectid = sect.sectid
order by section, roledescr, lastname, firstname;