Query to show total time spent of a technician for the current month_PGSQL

Query to show total time spent of a technician for the current month_PGSQL

Query show total time spent by technician for the current month regardless of the ticket created date.

PGSQL:

SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", wo.TITLE AS "Subject", rtdef.NAME AS "Request Type", ti.FIRST_NAME AS "Technician", sdo.NAME AS "Site", pd.PRIORITYNAME AS "Priority", LONGTODATE(wo.CREATEDTIME) AS "Created Time", LONGTODATE(wo.RESPONDEDTIME) AS "Responded Date", LONGTODATE(wo.COMPLETEDTIME) AS "Completed Time", LONGTODATE(wo.FR_DUETIME) AS "Response DueBy Time", wos.IS_FR_OVERDUE AS "First Response Overdue Status",rctd.FIRST_NAME "Time Spent Technician", TO_CHAR((sum(ct.TIMESPENT)/1000 || ' second')::interval, 'HH24:MI:SS') "Work Log Total Hours" FROM WorkOrder wo LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.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 SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID 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 WorkOrderToCharge wotoc ON wo.WORKORDERID=wotoc.WORKORDERID  LEFT JOIN ChargesTable ct ON wotoc.CHARGEID=ct.CHARGEID  LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID WHERE (wo.ISPARENT='1') AND ct.createdtime>=<from_thismonth>and ct.createdtime<=<to_thismonth>
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15 ORDER BY 15

NOTE: Date filter highlighted in the query can be modified and below parameters can be used instead.

How to compare date column with auto filled date templates?
  1. Here is the example for getting this week data - CREATEDTIME >= <from_thisweek> AND CREATEDTIME <= <to_thisweek>
    • <from_thisweek> - Starting date of this week
    • <to_thisweek> - Ending date of this week
  2. Available Date Templates
    • Today - <from_today> - <to_today>
    • This week - <from_thisweek> - <to_thisweek>
    • Last week - <from_lastweek> - <to_lastweek>
    • This month - <from_thismonth> - <to_thismonth>
    • Last month - <from_lastmonth> - <to_lastmonth>
    • This quarter - <from_thisquarter> - <to_thisquarter>
    • Last quarter - <from_lastquarter> - <to_lastquarter>
    • Yesterday - <from_yesterday> - <to_yesterday>
      New to ADManager Plus?

        New to ADSelfService Plus?

          Resources

              • Related Articles

              • Query to show total time spent for a ticket

                PGSQL: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", wo.TITLE AS "Subject", rtdef.NAME AS "Request Type", ti.FIRST_NAME AS "Technician", sdo.NAME AS "Site", ...
              • Query to show Total Onhold time_PGSQL

                PGSQL: 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", ...
              • Query to show tickets that shows total time spent of the ticket more than x hours

                Query to show tickets that shows total time spent of the ticket more than 8 hours: select ad.org_name "Account", sdo.name "Site", wo.workorderid "Request ID", au.first_name "Assigned Technician", cast((((sum(ct.timespent))/1000)/3600) as varchar(20)) ...
              • Query to show time spent by each technician in a ticket.

                Navigate to Reports->New Query Report and execute this report. Under Help->About, check the database you are using and use the appropriate query. MSSQL: SELECT wo.WORKORDERID "Request ID",wo.TITLE "Subject",cd.CATEGORYNAME "Category",scd.NAME ...
              • Query to get total worklog time spent on a request.

                Scenario: Request1 worklog1-Total time taken to resolve=1 hr 20 mins. worklog2- Total time taken to resolve=1hr 10mins. While generating a report the total time taken to resolve for request1 should be 2 hrs 30mins. Query: SELECT aau.FIRST_NAME AS ...