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 get complete SLA details. (PGSQL)
Tested in build PGSQL (14300) To get the complete list of SLA configured in the application. PGSQL: SELECT sdo.name "Site Name" , sla.slaname "SLA", MAX(sla.duebydays) "SLA Days", MAX(sla.duebyhours) "SLA Hours", MAX(sla.duebyminutes) "SLA Minutes" , ...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 ...