Query to show roles assigned to a technician with created date, status and department name (MSSQL & PGSQL)

Query to show roles assigned to a technician with created date, status and department name (MSSQL & PGSQL)

Tested in builds from PGSQL (14300) or MSSQL (14306)

Query:

select auser.first_name as "TECHNICIAN",  max(sd.employeeid) as "Employee ID",  longtodate(auser.createdtime) as "Creation Date", STRING_AGG (arole.name, ',') "Current Assigned Role", max(sd.status) "Current Status",  max(deptdef.deptname) "Department Name", max(sd.jobtitle) as "Job Title", max(sdorg.name) as "Location" 
from aaauser "auser" 
inner join portaltechnicians "ptech" on auser.user_id = ptech.userid 
left join sduser "sd" on ptech.userid = sd.userid 
left join userdepartment "userdept" on sd.userid = userdept.userid 
left join departmentdefinition "deptdef" on userdept.deptid = deptdef.deptid 
left join sitedefinition "sitedef" on deptdef.siteid = sitedef.siteid 
left join sdorganization "sdorg" on sitedef.siteid = sdorg.org_id 
left join aaalogin "alog" on auser.user_id= alog.user_id 
left join aaaaccount "acc" on alog.login_id = acc.login_id 
left join sdauthorizedrole "auth" on acc.account_id = auth.account_id
left join aaarole "arole" on auth.role_id = arole.role_id
group by auser.user_id, auser.FIRST_NAME, auser.createdtime

                  New to ADSelfService Plus?