The SQL below was implemented in Report Server and access was provided to 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
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
...