The SQL below was implemented in Report Server and access was provided to Keith Hirata.
SQL:
--Find all ACTIVE staff & students
-- ALL STAFFactive 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 , section sect, role
where pers.persid = ppos.persid
inner join section sect on ppos.sectid = sect.sectid
inner join role on and pers.roleid != role.roleid9
where and ppos.pposstatus = 'A'
and pers.roleid = role.roleid !=9
and ppos.sectid = sect.sectid
UNION ALL
-- ALL STUDENTSactive 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 , section sect, role,
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
where pers.persid = ppos.persid
and pers.roleid = 9
and pers.persuhuuid = STDLINK.stduhuuid
and ppos.pposstatus = 'A'
and pers.roleid = role.roleid = 9
and PLCLINK.plcStatus = 'A'
and PLCLINK.stdId = STDLINK.stdId
and PLCLINK.plcId = ppos.empId .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;