Active CSOC

SQL:

This is the Report Server SQL for CSOC to see their active staff:

--ALL Staff
select pers.perslastname as lastname, pers.persfirstname as firstname, role.roledescr, 
pers.persuhuuid as uhnumber, pers.persitsusername as username, ROUND(psempl.salary_annual/2080,2) as hourlypay, 
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
inner join psempl on pers.persuhuuid = psempl.employee_alt_id
where sect.sectname like 'CSOC%'
  and ppos.pposstatus = 'A'
  and role.roleid !=9
  and ppos.ppostype = 1

UNION ALL
--ALL Students
select STDLINK.stdlastname as lastname, STDLINK.stdfirstname as firstname, role.roledescr, 
pers.persuhuuid as uhnumber, pers.persitsusername as username, PLCLINK.plcpayvalue as hourlypay, 
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 sect.sectname like 'CSOC%'
  and ppos.pposstatus = 'A'
  and role.roleid = 9
and PLCLINK.plcId = ppos.empId 
and SUPLINK.aprid = 2
order by lastname, firstname

;