Query to show tickets created based on shift time ( PGSQL )
Tested in build PGSQL (14300)
Filter mentioned by converting into minutes.
PGSQL:
7.30 AM - 7.30 PM
SELECT wo.WORKORDERID AS "Request ID", pd.PRIORITYNAME AS "Priority", std.STATUSNAME AS "Request Status",
LONGTODATE(wo.CREATEDTIME) AS "Created Time",
extract(hour from to_timestamp(wo.createdtime/1000)::TIMESTAMP) "hh",
extract(minute from to_timestamp(wo.createdtime/1000)::TIMESTAMP) "minu",extract(hour from to_timestamp(wo.createdtime/1000)::TIMESTAMP)*60 + extract(minute from to_timestamp(wo.createdtime/1000)::TIMESTAMP) "total minutes" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID WHERE wo.ISPARENT='1' and
extract(hour from to_timestamp(wo.createdtime/1000)::TIMESTAMP)*60 + extract(minute from to_timestamp(wo.createdtime/1000)::TIMESTAMP) > 558
and extract(hour from to_timestamp(wo.createdtime/1000)::TIMESTAMP)*60 + extract(minute from to_timestamp(wo.createdtime/1000)::TIMESTAMP) < 1038
LONGTODATE(wo.CREATEDTIME) AS "Created Time",
extract(hour from to_timestamp(wo.createdtime/1000)::TIMESTAMP) "hh",
extract(minute from to_timestamp(wo.createdtime/1000)::TIMESTAMP) "minu",extract(hour from to_timestamp(wo.createdtime/1000)::TIMESTAMP)*60 + extract(minute from to_timestamp(wo.createdtime/1000)::TIMESTAMP) "total minutes" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID WHERE wo.ISPARENT='1' and
extract(hour from to_timestamp(wo.createdtime/1000)::TIMESTAMP)*60 + extract(minute from to_timestamp(wo.createdtime/1000)::TIMESTAMP) > 558
and extract(hour from to_timestamp(wo.createdtime/1000)::TIMESTAMP)*60 + extract(minute from to_timestamp(wo.createdtime/1000)::TIMESTAMP) < 1038
7.30 PM - 7.30 AM
SELECT wo.WORKORDERID AS "Request ID", pd.PRIORITYNAME AS "Priority", std.STATUSNAME AS "Request Status",
LONGTODATE(wo.CREATEDTIME) AS "Created Time",
extract(hour from to_timestamp(wo.createdtime/1000)::TIMESTAMP) "hh",
extract(minute from to_timestamp(wo.createdtime/1000)::TIMESTAMP) "minu",extract(hour from to_timestamp(wo.createdtime/1000)::TIMESTAMP)*60 + extract(minute from to_timestamp(wo.createdtime/1000)::TIMESTAMP) "total minutes" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID WHERE wo.ISPARENT='1' and
extract(hour from to_timestamp(wo.createdtime/1000)::TIMESTAMP)*60 + extract(minute from to_timestamp(wo.createdtime/1000)::TIMESTAMP) > 1158 or extract(hour from to_timestamp(wo.createdtime/1000)::TIMESTAMP)*60 + extract(minute from to_timestamp(wo.createdtime/1000)::TIMESTAMP) < 438
LONGTODATE(wo.CREATEDTIME) AS "Created Time",
extract(hour from to_timestamp(wo.createdtime/1000)::TIMESTAMP) "hh",
extract(minute from to_timestamp(wo.createdtime/1000)::TIMESTAMP) "minu",extract(hour from to_timestamp(wo.createdtime/1000)::TIMESTAMP)*60 + extract(minute from to_timestamp(wo.createdtime/1000)::TIMESTAMP) "total minutes" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID WHERE wo.ISPARENT='1' and
extract(hour from to_timestamp(wo.createdtime/1000)::TIMESTAMP)*60 + extract(minute from to_timestamp(wo.createdtime/1000)::TIMESTAMP) > 1158 or extract(hour from to_timestamp(wo.createdtime/1000)::TIMESTAMP)*60 + extract(minute from to_timestamp(wo.createdtime/1000)::TIMESTAMP) < 438
6 AM - 10 PM
SELECT wo.WORKORDERID AS "Request ID", pd.PRIORITYNAME AS "Priority", std.STATUSNAME AS "Request Status",
LONGTODATE(wo.CREATEDTIME) AS "Created Time",
extract(hour from to_timestamp(wo.createdtime/1000)::TIMESTAMP) "hh",
extract(minute from to_timestamp(wo.createdtime/1000)::TIMESTAMP) "minu",extract(hour from to_timestamp(wo.createdtime/1000)::TIMESTAMP)*60 + extract(minute from to_timestamp(wo.createdtime/1000)::TIMESTAMP) "total minutes" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID WHERE wo.ISPARENT='1' and
extract(hour from to_timestamp(wo.createdtime/1000)::TIMESTAMP)*60 + extract(minute from to_timestamp(wo.createdtime/1000)::TIMESTAMP) > 360
and extract(hour from to_timestamp(wo.createdtime/1000)::TIMESTAMP)*60 + extract(minute from to_timestamp(wo.createdtime/1000)::TIMESTAMP) < 1320
10 PM - 6 AM
SELECT wo.WORKORDERID AS "Request ID", pd.PRIORITYNAME AS "Priority", std.STATUSNAME AS "Request Status",
LONGTODATE(wo.CREATEDTIME) AS "Created Time",
extract(hour from to_timestamp(wo.createdtime/1000)::TIMESTAMP) "hh",
extract(minute from to_timestamp(wo.createdtime/1000)::TIMESTAMP) "minu",extract(hour from to_timestamp(wo.createdtime/1000)::TIMESTAMP)*60 + extract(minute from to_timestamp(wo.createdtime/1000)::TIMESTAMP) "total minutes" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID WHERE wo.ISPARENT='1' and
extract(hour from to_timestamp(wo.createdtime/1000)::TIMESTAMP)*60 + extract(minute from to_timestamp(wo.createdtime/1000)::TIMESTAMP) > 1320
and extract(hour from to_timestamp(wo.createdtime/1000)::TIMESTAMP)*60 + extract(minute from to_timestamp(wo.createdtime/1000)::TIMESTAMP) < 360
LONGTODATE(wo.CREATEDTIME) AS "Created Time",
extract(hour from to_timestamp(wo.createdtime/1000)::TIMESTAMP) "hh",
extract(minute from to_timestamp(wo.createdtime/1000)::TIMESTAMP) "minu",extract(hour from to_timestamp(wo.createdtime/1000)::TIMESTAMP)*60 + extract(minute from to_timestamp(wo.createdtime/1000)::TIMESTAMP) "total minutes" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID WHERE wo.ISPARENT='1' and
extract(hour from to_timestamp(wo.createdtime/1000)::TIMESTAMP)*60 + extract(minute from to_timestamp(wo.createdtime/1000)::TIMESTAMP) > 1320
and extract(hour from to_timestamp(wo.createdtime/1000)::TIMESTAMP)*60 + extract(minute from to_timestamp(wo.createdtime/1000)::TIMESTAMP) < 360
We have to calculate minutes in 24 hour format.
Example:
For 6 AM: 00:00 - 06:00=> 6 Hours (6*60 minutes)= 360 minutes.
For 10 PM: 00:00 - 22:00=> 22 Hours (22*60 Minutes)= 1320 Minutes​
New to ADSelfService Plus?
Related Articles
Query to show technician created time (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL & MSSQL: SELECT AaaUser.FIRST_NAME "Technician Name", aci.emailid "Email ID", LONGTODATE(AaaUser.createdtime) "Technician Created Time", SDUSER.STATUS "Status(Active/Inactive)" FROM AaaUser left ...Query to show Average response time for Category (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL: SELECT accountdefinition.org_name "Account",cd.categoryname "Category", TO_CHAR(((avg(wo.respondedtime)-avg(wo.createdtime))/1000 || ' second')::interval, 'HH24:MI:SS') "Avg Response Time" FROM ...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 ...Query to show total time spent of a technician for the current month-PGSQL
Working on Build's: 14500 Query show total time spent by technician for the current month regardless of the ticket created date based on worklogs added PGSQL: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", cd.CATEGORYNAME AS ...Query to show tickets first assigned today irrespective of created date
MSSQL: SELECT wo.WORKORDERID "Request ID", max(aau.FIRST_NAME) "Requester", max(dpt.DEPTNAME) "Department", max(wo.TITLE) "Subject", max(qd.QUEUENAME)"Group", max(ti.FIRST_NAME) "Technician", max(std.STATUSNAME) "Request Status", ...