Query for Support Rep list in DB

Query for Support Rep list in DB

Please run this query to find the list of Support Reps available with login in the system

select * from aaauser aaau join sduser sdu on aaau.user_id=sdu.userid inner join helpdeskcrew hd on sdu.userid=hd.technicianid inner join aaalogin aaal on hd.technicianid=aaal.user_id where sdu.STATUS='ACTIVE'


To know Support Rep with their role details

select aaau.user_id,aaau.first_name,de.departmentname,ar.name,aaal.name login from aaauser aaau join sduser sdu on aaau.user_id=sdu.userid inner join helpdeskcrew hd on sdu.userid=hd.technicianid join aaalogin aaal on hd.technicianid=aaal.user_id  left join departmentuser dep on dep.userid=sdu.userid left join user_departmentroles depr on dep.departmentuserid=depr.departmentuserid left join aaarole ar on depr.ROLEID = ar.ROLE_ID left join department de on dep.departmentid=de.departmentid where sdu.STATUS='ACTIVE'

Support Reps with BU & Group

Mysql

select aaau.user_id,aaau.first_name 'Support Rep',de.departmentname 'Business Unit',ar.name 'Role',aaal.name 'Login Name',qdef.queuename 'Group' from aaauser aaau join sduser sdu on aaau.user_id=sdu.userid inner join helpdeskcrew hd on sdu.userid=hd.technicianid join aaalogin aaal on hd.technicianid=aaal.user_id left join departmentuser dep on dep.userid=sdu.userid left join user_departmentroles depr on dep.departmentuserid=depr.departmentuserid left join aaarole ar on depr.ROLEID = ar.ROLE_ID left join department de on dep.departmentid=de.departmentid left join queue_technician at on aaau.user_id=at.technicianid left join queuedefinition qdef on at.queueid=qdef.queueid where sdu.STATUS='ACTIVE' order by qdef.queuename

Postgres
select aaau.user_id,aaau.first_name "Support Rep",de.departmentname "Business Unit",ar.name "Role",aaal.name "Login Name",qdef.queuename "Group" from aaauser aaau join sduser sdu on aaau.user_id=sdu.userid inner join helpdeskcrew hd on sdu.userid=hd.technicianid join aaalogin aaal on hd.technicianid=aaal.user_id left join departmentuser dep on dep.userid=sdu.userid left join user_departmentroles depr on dep.departmentuserid=depr.departmentuserid left join aaarole ar on depr.ROLEID = ar.ROLE_ID left join department de on dep.departmentid=de.departmentid left join queue_technician at on aaau.user_id=at.technicianid left join queuedefinition qdef on at.queueid=qdef.queueid where sdu.STATUS='ACTIVE' group by aaau.user_id,de.departmentname,ar.name,aaal.name,qdef.queuename order by aaau.first_name,departmentname,qdef.queuename

Support reps with their email address

select * from aaauser aaau join sduser sdu on aaau.user_id=sdu.userid inner join helpdeskcrew hd on sdu.userid=hd.technicianid inner join aaalogin aaal on hd.technicianid=aaal.user_id left join aaausercontactinfo aaauci on aaau.user_id=aaauci.user_id left join aaacontactinfo aaauc on aaauci.Contactinfo_id=aaauc.Contactinfo_id where sdu.STATUS='ACTIVE'

select aaau .user_id,aaauc.Emailid,aaal.name from aaauser aaau join sduser sdu on aaau.user_id=sdu.userid inner join helpdeskcrew hd on sdu.userid=hd.technicianid inner join aaalogin aaal on hd.technicianid=aaal.user_id left join aaausercontactinfo aaauci on aaau.user_id=aaauci.user_id left join aaacontactinfo aaauc on aaauci.Contactinfo_id=aaauc.Contactinfo_id where sdu.STATUS='ACTIVE'

Support reps without login

select * from aaauser aaau join sduser sdu on aaau.user_id=sdu.userid inner join helpdeskcrew hd on sdu.userid=hd.technicianid left join aaalogin aaal on hd.technicianid=aaal.user_id left join aaausercontactinfo aaauci on aaau.user_id=aaauci.user_id left join aaacontactinfo aaauc on aaauci.Contactinfo_id=aaauc.Contactinfo_id where sdu.STATUS='ACTIVE' and aaal.login_id is null

Group,Support Rep

SELECT que.QUEUEID, que.QUEUENAME, que.QUEUEDESCRIPTION, dep.DEPARTMENTNAME, aaau.first_name FROM QueueDefinition que Left Join Queue_Technician qt ON que.QUEUEID=qt.QUEUEID Left Join aaauser aaau  ON qt.TECHNICIANID = aaau.USER_ID left join sduser sdu on aaau.USER_ID=sdu.userid left join department dep on que.departmentid=dep.departmentid where sdu.STATUS='ACTIVE'