Query report for SLA report grouped by created time- (PGSQL )
Tested in Build PGSQL (14300)
Go to Reports-New Query Report and execute this query.
SELECT to_char(to_timestamp(wo.CREATEDTIME/1000),'DD/MM/YYYY') "Created Time",
count(wo.WORKORDERID) "Total Request",
count(case when ( std.ispending='0') THEN 1 ELSE NULL END) "Closed",
count(case when ( std.ispending='1') THEN 1 ELSE NULL END) "Open",
count(case when (wos.IS_FR_OVERDUE='1') THEN 1 ELSE NULL END) "Response SLA violated",
count(case when (wos.ISOVERDUE='1') THEN 1 ELSE NULL END) "Resolution SLA violated",
count(case when (wos.IS_FR_OVERDUE='0') THEN 1 ELSE NULL END) "Response SLA not violated" ,
count(case when (wos.ISOVERDUE='0') THEN 1 ELSE NULL END) "Resolution SLA not violated" ,
case when count(wo.workorderid) > 0 then cast(count(case when (wos.IS_FR_OVERDUE='1') THEN 1 ELSE NULL END) *100 as float) / cast(count(wo.workorderid) as float) else null end "% Response SLA Violated",
case when count(wo.workorderid) > 0 then cast(count(case when (wos.ISOVERDUE='1') THEN 1 ELSE NULL END) *100 as float) / cast(count(wo.workorderid) as float) else null end "% Resolution SLA Violated",
case when count(wo.workorderid) > 0 then cast(count(case when (wos.IS_FR_OVERDUE='0') THEN 1 ELSE NULL END) *100 as float) / cast(count(wo.workorderid) as float) else null end "% Response SLA Not Violated",
case when count(wo.workorderid) > 0 then cast(count(case when (wos.ISOVERDUE='0') THEN 1 ELSE NULL END) *100 as float) / cast(count(wo.workorderid) as float) else null end "% Resolution SLA Not Violated" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID
LEFT JOIN StatusDefinition std on wos.statusid=std.statusid
WHERE (wo.ISPARENT='1') AND (wo.is_catalog_template = 'false') AND wo.CREATEDTIME >= <from_thisweek> AND wo.CREATEDTIME <= <to_thisweek> GROUP BY to_char(to_timestamp(wo.CREATEDTIME/1000),'DD/MM/YYYY')
order by 1
New to ADSelfService Plus?
Related Articles
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 to show time elapsed in a ticket with SLA associated (PGSQL)
Tested in PGSQL build (14300) PGSQL: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", ti.FIRST_NAME AS "Technician", cd.CATEGORYNAME AS "Category", std.STATUSNAME AS "Request Status", LONGTODATE(wo.CREATEDTIME) "Created Time", ...Report to get list of Requesters with Requester created time and created by (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Kindly execute the report under Report -> New Query Report. SELECT AaaUser.FIRST_NAME "FullName", AaaLogin.NAME "LoginName", longtodate(aaauser.createdtime) "Created time", AaaContactInfo.EMAILID ...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 to show SLA assigned to tickets
Working on Builds: 14500 and above Database: PGSQL & MSSQL: SELECT slad.slaname "SLA", ad.ORG_NAME AS "Account", wo.WORKORDERID AS "Request ID", LONGTODATE(wo.CREATEDTIME) AS "Created Time", ti.FIRST_NAME AS "Technician", rtdef.NAME AS "Request Type" ...