Query to get Request Ageing info.
Version : 14304
DB : PGSQL
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",
sdo.NAME AS "Site",
mdd.MODENAME AS "Request Mode",
cri.FIRST_NAME AS "Created By",
regionDef.REGIONNAME AS "Region",
dpt.DEPTNAME AS "Department",
CAST((((wo.RESOLVEDTIME-wo.createdtime)/1000)/(3600*24)) AS VARCHAR(10)) "Ageing of ticket (No of Days)" 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
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 AccountSiteMapping asm ON wo.SITEID=asm.SITEID
LEFT JOIN AccountDefinition ad ON asm.ACCOUNTID=ad.ORG_ID
LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID
LEFT JOIN RegionDefinition regionDef ON siteDef.REGIONID=regionDef.REGIONID WHERE wo.ISPARENT='1' AND wo.RESOLVEDTIME > 0 and wo.createdtime >= <from_lastmonth> AND wo.createdtime <= <to_lastmonth>
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",
sdo.NAME AS "Site",
mdd.MODENAME AS "Request Mode",
cri.FIRST_NAME AS "Created By",
regionDef.REGIONNAME AS "Region",
dpt.DEPTNAME AS "Department",
CAST((((wo.RESOLVEDTIME-wo.createdtime)/1000)/(3600*24)) AS VARCHAR(10)) "Ageing of ticket (No of Days)" 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
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 AccountSiteMapping asm ON wo.SITEID=asm.SITEID
LEFT JOIN AccountDefinition ad ON asm.ACCOUNTID=ad.ORG_ID
LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID
LEFT JOIN RegionDefinition regionDef ON siteDef.REGIONID=regionDef.REGIONID WHERE wo.ISPARENT='1' AND wo.RESOLVEDTIME > 0 and wo.createdtime >= <from_lastmonth> AND wo.createdtime <= <to_lastmonth>