Query to list account wise technicians and their respective support group details

Query to list account wise technicians and their respective support group details

Tested in: 14610
Compatible with: Both Postgres and MSSQL DB. 

Query 1:

To return technicians and respective support group details from all accounts:

SELECT ad.ORG_NAME AS "Account Name", au.FIRST_NAME AS "Technician Name", qd.QUEUENAME AS "Support Group" FROM AccountDefinition ad INNER JOIN TechnicianAccountMapping tam ON ad.ORG_ID = tam.ACCOUNTID INNER JOIN PortalTechnicians pt ON tam.TECHNICIANID = pt.USERID  INNER JOIN AaaUser au ON pt.USERID = au.USER_ID LEFT JOIN Queue_Technician qt ON pt.USERID = qt.TECHNICIANID LEFT JOIN QueueDefinition qd ON qt.QUEUEID = qd.QUEUEID 


Query 2:

To return technicians and respective support group details from an individual account:

SELECT ad.ORG_NAME AS "Account Name", au.FIRST_NAME AS "Technician Name", qd.QUEUENAME AS "Support Group" FROM AccountDefinition ad INNER JOIN TechnicianAccountMapping tam ON ad.ORG_ID = tam.ACCOUNTID INNER JOIN PortalTechnicians pt ON tam.TECHNICIANID = pt.USERID  INNER JOIN AaaUser au ON pt.USERID = au.USER_ID LEFT JOIN Queue_Technician qt ON pt.USERID = qt.TECHNICIANID LEFT JOIN QueueDefinition qd ON qt.QUEUEID = qd.QUEUEID WHERE ad.ORG_NAME ='MY ORG Inc'

Where, "MY ORG Inc" can be replaced by the desired account name. 

                  New to ADSelfService Plus?