Query to show tickets created and resolved during weekdays and weekends-PGSQL

Query to show tickets created and resolved during weekdays and weekends-PGSQL

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

                  New to ADSelfService Plus?