Working on Builds: 14500
1. Mon-Sat 7 PM - 6 AM:
a. Created tickets:
SELECT wo.WORKORDERID AS "Request ID", mdd.MODENAME AS "Request Mode", aau.FIRST_NAME AS "Requester", dpt.DEPTNAME AS "Department", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", icd.NAME AS "Item", wo.TITLE AS "Subject", longtodate(wo.CREATEDTIME) AS "Created Time", pd.PRIORITYNAME AS "Priority" 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 DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID 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 ItemDefinition icd ON wos.ITEMID=icd.ITEMID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID WHERE wo.ISPARENT='1' AND ((extract(HOUR
FROM to_timestamp(wo.createdtime/1000)::TIMESTAMP) < 6
OR extract(HOUR
FROM to_timestamp(wo.createdtime/1000)::TIMESTAMP) > 19)
AND extract(dow
FROM to_timestamp(wo.createdtime/1000)::TIMESTAMP) IN (1,2,3,4,5,6))
ORDER BY 1 DESC
b. Resolved tickets:
SELECT wo.WORKORDERID AS "Request ID", mdd.MODENAME AS "Request Mode", aau.FIRST_NAME AS "Requester", dpt.DEPTNAME AS "Department", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", icd.NAME AS "Item", wo.TITLE AS "Subject", longtodate(wo.CREATEDTIME) AS "Created Time", LONGTODATE(WO.RESOLVEDTIME) "Resolved Time", pd.PRIORITYNAME AS "Priority" 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 DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID 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 ItemDefinition icd ON wos.ITEMID=icd.ITEMID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID WHERE wo.ISPARENT='1' AND ((extract(HOUR
FROM to_timestamp(wo.resolvedtime/1000)::TIMESTAMP) < 6
OR extract(HOUR
FROM to_timestamp(wo.resolvedtime/1000)::TIMESTAMP) > 19)
AND extract(dow
FROM to_timestamp(wo.resolvedtime/1000)::TIMESTAMP) IN (1,2,3,4,5,6)) AND (wo.RESOLVEDTIME != 0) AND (wo.RESOLVEDTIME IS NOT NULL)
ORDER BY 1 DESC
2. Sunday tickets:
a. Created during Sunday:
SELECT wo.WORKORDERID AS "Request ID", mdd.MODENAME AS "Request Mode", aau.FIRST_NAME AS "Requester", dpt.DEPTNAME AS "Department", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", icd.NAME AS "Item", wo.TITLE AS "Subject", longtodate(wo.CREATEDTIME) AS "Created Time", LONGTODATE(WO.RESOLVEDTIME) "Resolved Time", pd.PRIORITYNAME AS "Priority" 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 DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID 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 ItemDefinition icd ON wos.ITEMID=icd.ITEMID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID WHERE wo.ISPARENT='1' AND extract(dow FROM to_timestamp(wo.createdtime/1000)::TIMESTAMP) IN (0)
ORDER BY 1 DESC
b. Resolved during Sunday:
SELECT wo.WORKORDERID AS "Request ID", mdd.MODENAME AS "Request Mode", aau.FIRST_NAME AS "Requester", dpt.DEPTNAME AS "Department", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", icd.NAME AS "Item", wo.TITLE AS "Subject", longtodate(wo.CREATEDTIME) AS "Created Time", LONGTODATE(WO.RESOLVEDTIME) "Resolved Time", pd.PRIORITYNAME AS "Priority" 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 DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID 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 ItemDefinition icd ON wos.ITEMID=icd.ITEMID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID WHERE wo.ISPARENT='1' AND extract(dow FROM to_timestamp(wo.resolvedtime/1000)::TIMESTAMP) IN (0)
ORDER BY 1 DESC