Query to show Total Onhold time ( PGSQL )
Tested in build PGSQL (14300)
PGSQL:
SELECT LONGTODATE(wo.CREATEDTIME) AS "Created Time", wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", wo.TITLE AS "Subject", pd.PRIORITYNAME AS "Priority", std.STATUSNAME AS "Request Status", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", qd.QUEUENAME AS "Group", ti.FIRST_NAME AS "Technician", LONGTODATE(wos.LAST_TECH_UPDATE) AS "Last Update Time", LONGTODATE(wo.RESOLVEDTIME) AS "Resolved Time", rrs.RESOLUTION AS "Resolution", rtdef.NAME AS "Request Type", ad.ORG_NAME AS "Account", sdo.NAME AS "Site", mdd.MODENAME AS "Request Mode", LONGTODATE(wo.RESPONDEDTIME) AS "Responded Date", cri.FIRST_NAME AS "Created By", (select (sum(wsi.TIMESPENT/60))||':'||(sum(wsi.TIMESPENT))%60 from
wo_assessment woa1
inner join wo_status_info wsi on woa1.assessmentid=wsi.assessmentid
left join statusdefinition sd1 on wsi.statusid=sd1.statusid where sd1.statusname like '%hold%' and wo.workorderid=woa1.workorderid) "Total Hold Time" FROM WorkOrder wo LEFT JOIN ModeDefinition mdd ON wo.MODEID=mdd.MODEID LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN SDUser crd ON wo.CREATEDBYID=crd.USERID LEFT JOIN AaaUser cri ON crd.USERID=cri.USER_ID LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN RequestTypeDefinition rtdef ON wos.REQUESTTYPEID=rtdef.REQUESTTYPEID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID LEFT JOIN RequestResolver rrr ON wo.WORKORDERID=rrr.REQUESTID LEFT JOIN RequestResolution rrs ON rrr.REQUESTID=rrs.REQUESTID LEFT JOIN AccountSiteMapping asm ON wo.SITEID=asm.SITEID LEFT JOIN AccountDefinition ad ON asm.ACCOUNTID=ad.ORG_ID WHERE wo.createdtime>=<from_thisquarter> and wo.createdtime<=<to_thisquarter>
NOTE: Date filter highlighted in the query can be modified and below parameters can be used instead.
How to compare date column with auto filled date templates?
- Here is the example for getting this week data - CREATEDTIME >= <from_thisweek> AND CREATEDTIME <= <to_thisweek>
- <from_thisweek> - Starting date of this week
- <to_thisweek> - Ending date of this week
- Available Date Templates
- Today - <from_today> - <to_today>
- This week - <from_thisweek> - <to_thisweek>
- Last week - <from_lastweek> - <to_lastweek>
- This month - <from_thismonth> - <to_thismonth>
- Last month - <from_lastmonth> - <to_lastmonth>
- This quarter - <from_thisquarter> - <to_thisquarter>
- Last quarter - <from_lastquarter> - <to_lastquarter>
- Yesterday - <from_yesterday> - <to_yesterday>
With HH:MM:SS
SELECT LONGTODATE(wo.CREATEDTIME) AS "Created Time", wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", wo.TITLE AS "Subject", pd.PRIORITYNAME AS "Priority", std.STATUSNAME AS "Request Status", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", idef.name "Item", qd.QUEUENAME AS "Group", ti.FIRST_NAME AS "Technician", LONGTODATE(wos.LAST_TECH_UPDATE) AS "Last Updated Time", LONGTODATE(wo.RESOLVEDTIME) AS "Resolved Time", rrs.RESOLUTION AS "Resolution", rtdef.NAME AS "Request Type", ad.ORG_NAME AS "Account", sdo.NAME AS "Site", mdd.MODENAME AS "Request Mode", LONGTODATE(wo.RESPONDEDTIME) AS "Responded Date", cri.FIRST_NAME AS "Created By", (select (sum(wsi.TIMESPENT/60))||':'||(sum(wsi.TIMESPENT))%60||':'||(sum(wsi.timespent/1000)%60)%60 from
wo_assessment woa1
inner join wo_status_info wsi on woa1.assessmentid=wsi.assessmentid
left join statusdefinition sd1 on wsi.statusid=sd1.statusid where sd1.statusname like '%hold%' and wo.workorderid=woa1.workorderid) "Total Hold Time"
FROM WorkOrder wo
LEFT JOIN ModeDefinition mdd ON wo.MODEID=mdd.MODEID
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN SDUser crd ON wo.CREATEDBYID=crd.USERID
LEFT JOIN AaaUser cri ON crd.USERID=cri.USER_ID
LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID
LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN itemdefinition idef ON wos.itemid=idef.itemid
JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID
LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
JOIN AaaUser ti ON td.USERID=ti.USER_ID
LEFT JOIN RequestTypeDefinition rtdef ON wos.REQUESTTYPEID=rtdef.REQUESTTYPEID
LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
LEFT JOIN RequestResolver rrr ON wo.WORKORDERID=rrr.REQUESTID
LEFT JOIN RequestResolution rrs ON rrr.REQUESTID=rrs.REQUESTID
LEFT JOIN AccountSiteMapping asm ON wo.SITEID=asm.SITEID
LEFT JOIN AccountDefinition ad ON asm.ACCOUNTID=ad.ORG_ID
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21
New to ADSelfService Plus?
Related Articles
Query to show total time taken to resolve and total time onhold ( MSSQL )
Tested in build MSSQL (14306) SELECT "wo"."WORKORDERID" AS "Request ID", "mdd"."MODENAME" AS "Request Mode", "qd"."QUEUENAME" AS "Group", "aau"."FIRST_NAME" AS "Requester", "cd"."CATEGORYNAME" AS "Category", "scd"."NAME" AS "Subcategory", ...Query to show when Onhold was placed for a request (PGSQL)
Tested in Build PGSQL (14300) PGSQL: SELECT wo.WORKORDERID AS "Request ID", sdo.NAME AS "Site", cd.CATEGORYNAME AS "Category", longtodate(wo.CREATEDTIME) AS "Created Time", std.STATUSNAME AS "Current Request Status", longtodate(wo.COMPLETEDTIME) AS ...Query to show total time spent for a ticket ( PGSQL)
Tested in PGSQL build (14300) Database: PGSQL SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", wo.TITLE AS "Subject", rtdef.NAME AS "Request Type", ti.FIRST_NAME AS ...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 total time spent of a technician for the current month-PGSQL
Working on Build's: 14500 Query show total time spent by technician for the current month regardless of the ticket created date based on worklogs added PGSQL: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", cd.CATEGORYNAME AS ...