Content Comparison

The SQL below was implemented in Report Server and access was provided to Keith Hirata.

SQL:

--Find all ACTIVE staff & students

-- active staffALL 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 , section sect, role
where on pers.persid = ppos.persid
and inner join section sect on ppos.sectid = sect.sectid
inner join role on pers.roleid ! = 9role.roleid
and where ppos.pposstatus = 'A'
  and pers.roleid = role.roleid
and ppos.sectid = sect.sectid!=9

UNION ALL
-- active studentsALL 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 , section sect, role,
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,
STDLINK on pers.persuhuuid = STDLINK.stduhuuid
inner join SECEADM.placement@secedb_link PLCLINK,
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
where pers.persid = ppos.persid
and pers.roleid = 9
and pers.persuhuuid = STDLINK.stduhuuid
and on SUPLINK.contactid = CONLINK.contactid  
where ppos.pposstatus = 'A'
  and pers.roleid = role.roleid
and PLCLINK.plcStatus = 'A'
and PLCLINK.stdId = STDLINK.stdId = 9
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;