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 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)) +'Hrs '+cast(((((sum(ct.timespent))/1000)/60)) % 60 as varchar(20))+'Mins '+ cast((((sum(ct.timespent))%1000)%60)
as varchar(20))+ 'Secs' "Time Spent" from chargestable ct
LEFT JOIN WorkOrderToCharge wotoc ON ct.CHARGEID=wotoc.CHARGEID 
LEFT JOIN WorkOrder wo ON wotoc.WORKORDERID=wo.WORKORDERID
LEFT JOIN SWorkorderstates swo ON swo.workorderid=wo.workorderid
left join workorderstates on wo.workorderid=workorderstates.workorderid
LEFT JOIN Categorydefinition ctd ON ctd.categoryid=swo.categoryid 
LEFT JOIN Statusdefinition std ON std.statusid=swo.statusid
LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID 
LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID 
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
left join aaauser au on workorderstates.ownerid=au.user_id
left join sitedefinition sd ON wo.siteid=sd.siteid 
left join sdorganization sdo ON sd.siteid=sdo.org_id
INNER JOIN AccountSiteMapping asm ON wo.siteid=asm.siteid 
INNER JOIN AccountDefinition ad ON asm.accountid=ad.org_id 
LEFT JOIN StatusDefinition std2 ON workorderstates.STATUSID=std2.STATUSID
WHERE  ( ( std2.STATUSNAME = 'Scheduled' ) OR ( std2.STATUSNAME = 'Open' ) ) AND wo.CREATEDTIME >= <from_thisyear> AND wo.CREATEDTIME <= <to_thisyear> group by wo.workorderid, ad.org_name, sdo.name, au.first_name having cast((((sum(ct.timespent))/1000)/3600) as varchar(20)) > 8  ORDER BY 1

NOTE:

2.  Number 8 highlighted in the query shows the number of hours, can be modified. Also, status names are mentioned, if required can modify or remove the filter.

2. 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>

          • 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 tickets older than 30 days_MSSQL

            MSSQL: SELECT wo.WORKORDERID "Request ID",ti.FIRST_NAME "Technician",std.STATUSNAME "Request Status",wo.TITLE "Subject",aau.FIRST_NAME AS "Requester Name", pd.PRIORITYNAME as "Priority", adef.ORG_NAME as "Account", sdo.NAME as "Site", ...
          • Query to show Count of tickets based on OP Hrs_ MSSQL

            MSSQL: OP Hrs: (8.30 - 18.30) SELECT  qd.queuename "Group", "pd"."PRIORITYNAME" AS "Priority", ad.org_name "Account", count(wo.workorderid) "Total Tickets"  FROM WorkOrder wo LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN ...
          • 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 ...
          • Query to show total requests and changes with its total worklog hours of a technician _ MSSQL

            MSSQL: Requests: SELECT rctd.first_name "Technician", count(wo.workorderid) "Sum of tickets handled by him", convert(varchar(5),(sum(ct.TIMESPENT))/1000/3600)+':'+convert(varchar(5),((sum(ct.TIMESPENT))/1000)%3600/60)+':'+convert(varchar(5), ...