Query to list Technician Availability Chart Query Report (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306)
Please go to Reports-New Query Report and execute the below query
MSSQL
SELECT cre.FIRST_NAME "Created By",
LONGTODATE(TechUnavailability.CREATEDDATE) "Created Date",
avail.FIRST_NAME "Technician on Leave",
back.FIRST_NAME "Backup Technician",
LONGTODATE(DateUnAvailability.LEAVEDATE) "Leave Date",
LeaveTypeDefinition.NAME "Leave Type",
TechUnavailability.COMMENTS "Comments" FROM TechUnavailability
LEFT JOIN DateUnAvailability ON TechUnavailability.LEAVEID=DateUnAvailability.LEAVEID
LEFT JOIN LeaveTypeDefinition ON TechUnavailability.LEAVETYPEID=LeaveTypeDefinition.LEAVETYPEID
LEFT JOIN AaaUser cre ON TechUnavailability.CREATEDBY=cre.USER_ID
LEFT JOIN AaaUser avail ON TechUnavailability.TECHNICIANID=avail.USER_ID
LEFT JOIN AaaUser back ON TechUnavailability.Backuptechnicianid=back.USER_ID
ORDER BY 3,4
PGSQL
SELECT cre.FIRST_NAME "Created By",
LONGTODATE(TechUnavailability.CREATEDDATE) "Created Date",
avail.FIRST_NAME "Technician on Leave",
back.FIRST_NAME "Backup Technician",
LONGTODATE(DateUnAvailability.LEAVEDATE) "Leave Date",
LeaveTypeDefinition.NAME "Leave Type",
TechUnavailability.COMMENTS "Comments" FROM TechUnavailability
LEFT JOIN DateUnAvailability ON TechUnavailability.LEAVEID=DateUnAvailability.LEAVEID
LEFT JOIN LeaveTypeDefinition ON TechUnavailability.LEAVETYPEID=LeaveTypeDefinition.LEAVETYPEID
LEFT JOIN AaaUser cre ON TechUnavailability.CREATEDBY=cre.USER_ID
LEFT JOIN AaaUser avail ON TechUnavailability.TECHNICIANID=avail.USER_ID
LEFT JOIN AaaUser back ON TechUnavailability.Backuptechnicianid=back.USER_ID
ORDER BY 3,4
LONGTODATE(TechUnavailability.CREATEDDATE) "Created Date",
avail.FIRST_NAME "Technician on Leave",
back.FIRST_NAME "Backup Technician",
LONGTODATE(DateUnAvailability.LEAVEDATE) "Leave Date",
LeaveTypeDefinition.NAME "Leave Type",
TechUnavailability.COMMENTS "Comments" FROM TechUnavailability
LEFT JOIN DateUnAvailability ON TechUnavailability.LEAVEID=DateUnAvailability.LEAVEID
LEFT JOIN LeaveTypeDefinition ON TechUnavailability.LEAVETYPEID=LeaveTypeDefinition.LEAVETYPEID
LEFT JOIN AaaUser cre ON TechUnavailability.CREATEDBY=cre.USER_ID
LEFT JOIN AaaUser avail ON TechUnavailability.TECHNICIANID=avail.USER_ID
LEFT JOIN AaaUser back ON TechUnavailability.Backuptechnicianid=back.USER_ID
ORDER BY 3,4
New to ADSelfService Plus?
Related Articles
Query to list the custom and query reports and the technician created (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Use case The query displays the custom reports and query reports saved by technicians. Query SELECT custrepdet.report_name "Report Name", au.first_name"Created By" from customreportquery custrep ...Query report to extract Active Technician Login and IP Address (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) Go to Reports- New Query Report and execute this query. select DISTINCT ac.ACCOUNT_ID as "Login Account ID", au.FIRST_NAME as "Technician Name ( First Name )", count(acs.SESSION_ID) as "Number of ...Query to show technician created time (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL & MSSQL: SELECT AaaUser.FIRST_NAME "Technician Name", aci.emailid "Email ID", LONGTODATE(AaaUser.createdtime) "Technician Created Time", SDUSER.STATUS "Status(Active/Inactive)" FROM AaaUser left ...Query report to get the successfully scanned assets (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Query report to get the successfully scanned assets with audit status, state , Serial no and site. SELECT max(resource.resourcename) "Asset Name", max(resource.SERIALNO) AS "Org Serial Number", ...Query to show Technician leave details (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) select au.first_name "Technician", ltd.name "Leave type", longtodate(du.leavedate) "Leave date", ta.comments "Leave Comments" from techunavailability ta left join aaauser au on ...