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

Working ON Builds: 14500


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>

                  New to ADSelfService Plus?