Query to get Timespent on Request for particular group
DB: PGSQL
Version: 8107
OUTPUT:
select wof.UDF_CHAR19 AS "SSTF Number",
org.name as "Account" ,
rc.workorderid as "Request ID",
wo.title as "Subject",
longtodate(wo.createdtime),
qd.queuename AS "Group",
std.STATUSNAME AS "Request Status" ,
TO_CHAR((sum(rc.MM2COMPLETEREQUEST)/1000 || ' second')::interval, 'HH24:MI:SS') "Total Time spent" from RequestCharges rc
left join workorder wo on rc.workorderid=wo.workorderid
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN WorkOrder_Fields wof ON wo.WORKORDERID=wof.WORKORDERID
left join WorkOrder_Account wc on wo.workorderid=wc.workorderid
left join aaaorganization org on wc.accountid=org.org_id
where wo.CREATEDTIME >= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2021-08-13 00:00:00') * 1000 AS BIGINT) AND wo.CREATEDTIME <= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2021-09-01 18:00:00') * 1000 AS BIGINT) and (std.STATUSNAME='Open' OR std.STATUSNAME='Onhold' OR std.STATUSNAME='Hold-Awaiting End User Response' OR std.STATUSNAME='Monitor' OR std.STATUSNAME='Acknowledge' OR std.STATUSNAME='Awaiting Vendor') and qd.queuename in ('Warranty Group','01')
group by wof.UDF_CHAR19,rc.workorderid, wo.workorderid, wc.workorderid ,org.name,std.STATUSNAME,qd.queuename order by 2
org.name as "Account" ,
rc.workorderid as "Request ID",
wo.title as "Subject",
longtodate(wo.createdtime),
qd.queuename AS "Group",
std.STATUSNAME AS "Request Status" ,
TO_CHAR((sum(rc.MM2COMPLETEREQUEST)/1000 || ' second')::interval, 'HH24:MI:SS') "Total Time spent" from RequestCharges rc
left join workorder wo on rc.workorderid=wo.workorderid
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN WorkOrder_Fields wof ON wo.WORKORDERID=wof.WORKORDERID
left join WorkOrder_Account wc on wo.workorderid=wc.workorderid
left join aaaorganization org on wc.accountid=org.org_id
where wo.CREATEDTIME >= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2021-08-13 00:00:00') * 1000 AS BIGINT) AND wo.CREATEDTIME <= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2021-09-01 18:00:00') * 1000 AS BIGINT) and (std.STATUSNAME='Open' OR std.STATUSNAME='Onhold' OR std.STATUSNAME='Hold-Awaiting End User Response' OR std.STATUSNAME='Monitor' OR std.STATUSNAME='Acknowledge' OR std.STATUSNAME='Awaiting Vendor') and qd.queuename in ('Warranty Group','01')
group by wof.UDF_CHAR19,rc.workorderid, wo.workorderid, wc.workorderid ,org.name,std.STATUSNAME,qd.queuename order by 2