Query to show total requests and changes with its total worklog hours of a technician _ MSSQL

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),
(((sum(ct.TIMESPENT)))/1000%60))  AS "Sum of total work log hours" FROM WorkOrder wo 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 left join workorder_queue woq on wo.workorderid=woq.workorderid
left join queuedefinition qd on woq.queueid=qd.queueid  WHERE (wo.ISPARENT='1') AND wo.createdtime>=<from_lastmonth> and wo.createdtime<=<to_lastmonth> group by rctd.first_name

Changes:

SELECT rctd.first_name "Technician",
count(Ch.Changeid) "Sum of changes handled by him",
convert(varchar(5),(sum(ct.TIMESPENT))/1000/3600)+':'+convert(varchar(5),((sum(ct.TIMESPENT))/1000)%3600/60)+':'+convert(varchar(5),
(((sum(ct.TIMESPENT)))/1000%60))  AS "Sum of total work log hours" FROM ChargesTable ct
LEFT JOIN ChangeToCharge chtoc ON ct.CHARGEID=chtoc.CHARGEID
LEFT JOIN ChangeDetails ch ON chtoc.CHANGEID=ch.CHANGEID
LEFT JOIN TaskToCharge tkc ON ct.CHARGEID=tkc.CHARGEID
LEFT JOIN  TaskDetails tk ON tkc.TASKID=tk.TASKID
LEFT JOIN ChangeToTaskDetails ctk ON tk.TASKID=ctk.TASKID
LEFT JOIN ChangeDetails ch2 ON ctk.CHANGEID=ch2.CHANGEID
LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID
 LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID 
left join queuedefinition qd on ch.groupid=qd.queueid
LEFT JOIN AccountSiteMapping asm ON ch.siteid=asm.siteid
LEFT JOIN AccountDefinition ad ON asm.accountid=ad.org_id
WHERE (ctk.CHANGEID IS NOT NULL OR ch.CHANGEID IS NOT NULL) AND ch.createdtime>=<from_lastmonth> and ch.createdtime<=<to_lastmonth> group by rctd.first_name

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>
      • 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 time taken to resolve and total time onhold _ MSSQL

        MSSQL: SELECT "wo"."WORKORDERID" AS "Request ID", "mdd"."MODENAME" AS "Request Mode", "qd"."QUEUENAME" AS "Group", "aau"."FIRST_NAME" AS "Requester", "cd"."CATEGORYNAME" AS "Category", "scd"."NAME" AS "Subcategory", "urgdef"."NAME" AS "Urgency", ...
      • 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 Last added worklog of a ticket _MSSQL

        MSSQL: SELECT wo.WORKORDERID AS "Ticket Number", pd.PRIORITYNAME AS "Priority", cd.CATEGORYNAME AS "Category", qd.QUEUENAME AS "Group", ti.FIRST_NAME AS "Technician", aau.FIRST_NAME AS "Requester", Wo.title "Subject", wotodesc.FULLDESCRIPTION AS ...
      • Query to show both requests and task worklog time spent together

        DB : MSSQL Timespent for both tasks and requests select wo.workorderid "Request ID", aau.FIRST_NAME "Requester", cast((((sum(ct.timespent))/1000)/3600) as varchar(20)) +'Hrs '+cast(((((sum(ct.timespent))/1000)/60)) % 60 as varchar(20))+'Mins '+ ...