Query to show users of Organization Roles for each account (MSSQL & PGSQL)
T
ested in Build PGSQL (14300) or MSSQL (14306)
select ad.org_name "Account Name", org.orgrolename "ORG Role Name", aau.first_name "User" from orgroles org left join roletousermapping rtu ON org.orgroleid=rtu.roleid LEFT JOIN roletouseraccountmapping rum ON rtu.rumid=rum.rumid LEFT JOIN sduser sdu ON rtu.userid=sdu.userid LEFT JOIN aaauser aau ON sdu.userid=aau.user_id LEFT JOIN accountdefinition ad ON rum.accountid=ad.org_id WHERE ORGROLENAME='$AM_(ACCOUNT_MANAGER)$' OR ORGROLENAME='$SDM_(SERVICE_DELIVERY_MANAGER)$' AND sdu.status='ACTIVE'
group by ad.org_name, org.orgrolename, aau.first_name ORDER BY org.orgrolename
New to ADSelfService Plus?
Related Articles
Query to show Average response time for Category (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL: SELECT accountdefinition.org_name "Account",cd.categoryname "Category", TO_CHAR(((avg(wo.respondedtime)-avg(wo.createdtime))/1000 || ' second')::interval, 'HH24:MI:SS') "Avg Response Time" FROM ...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", ...Query to show tickets created based on shift time ( PGSQL )
Tested in build PGSQL (14300) Filter mentioned by converting into minutes. PGSQL: 7.30 AM - 7.30 PM SELECT wo.WORKORDERID AS "Request ID", pd.PRIORITYNAME AS "Priority", std.STATUSNAME AS "Request Status", LONGTODATE(wo.CREATEDTIME) AS "Created ...Query to show count of tickets account based (PGSQL)
Tested in Build PGSQL (14300) Please go to Reports-New Query Report and execute this report. select concat(ad.org_name, ' - ', tmp.wo_count), w.workorderid "Request ID",to_timestamp((w.createdtime)/1000)::TIMESTAMP "Created ...Query to show technician hop count (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) SELECT wo.WORKORDERID "Request ID", LONGTODATE(wo.CREATEDTIME) AS "Created Time", qd.QUEUENAME "Current Group",aau.FIRST_NAME AS "Requester", ti.FIRST_NAME "Technician", ad.ORG_NAME AS "Account", ...