Query to get Request details with resolution and resolved time calculation.

Version : 10537

DB : Postgres

Output:

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",
case when wo.RESOLVEDTIME > 0 THEN CAST((((wo.RESOLVEDTIME-wo.createdtime)/1000)/(3600*24)) AS VARCHAR(10)) ELSE '-' END AS "No of Days (Resolution Given)" ,
case when wo.RESOLVEDTIME = 0 THEN cast(extract(epoch from(now()::TIMESTAMP - to_timestamp(wo.createdtime/1000)::TIMESTAMP))/3600/24 as integer) ELSE 0 END AS "No of Days (Resolution Not Given)",
(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 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 WHERE wo.ISPARENT='1' AND wo.createdtime >= DATETOLONG('2022-11-30 00:00:00') AND wo.createdtime <= DATETOLONG('2023-01-31 23:59:59') AND (sdo.NAME='DVVNL(Agra)' OR sdo.NAME='KESCO(Kanpur)' OR sdo.NAME='MVVNL(Lucknow)' OR sdo.NAME='PuVVNL (Varanasi)' OR sdo.NAME='PVVNL (Merut)' OR sdo.NAME = 'UPPCL' OR sdo.NAME = 'UPTCL') group by wo.createdtime,wo.workorderid,aau.first_name,pd.priorityname,std.statusname,cd.categoryname,scd.name,idef.name,qd.queuename,ti.first_name,wos.last_tech_update,rrs.resolution,rtdef.name,ad.org_name,sdo.name,mdd.modename,cri.first_name