Query to get Time calculation of request from created time to resolved / closed status
Version : 10.5
DB : PGSQL
SELECT wo.WORKORDERID AS "Request ID",
case when (wo.completedtime=0) then null else TO_CHAR(((wo.completedtime-wo.createdtime)/1000 || ' second')::interval, 'HH24:MI:SS') end "Completed Date - created Date",
case when (wo.resolvedtime=0) then null else TO_CHAR(((wo.resolvedtime-wo.createdtime)/1000 || ' second')::interval, 'HH24:MI:SS') end "resolved Date - created Date",
std.STATUSNAME AS "Request Status" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
where std.statusname = 'Closed' or std.statusname = 'Resolved'
case when (wo.completedtime=0) then null else TO_CHAR(((wo.completedtime-wo.createdtime)/1000 || ' second')::interval, 'HH24:MI:SS') end "Completed Date - created Date",
case when (wo.resolvedtime=0) then null else TO_CHAR(((wo.resolvedtime-wo.createdtime)/1000 || ' second')::interval, 'HH24:MI:SS') end "resolved Date - created Date",
std.STATUSNAME AS "Request Status" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
where std.statusname = 'Closed' or std.statusname = 'Resolved'