Time Spent on each module

Time Spent on each module

This Report helps us to know the time spent by technicians on each module. 

The Time spent report provides a step-by-step record by which time-related data can be traced to their source and provides a complete history of all Technician activities with a time stamp.

With this data we can exactly know how long each type of modules takes, you’ll be able to adjust and improve your Productivity.

PGSQL:
 
SELECT COALESCE(wtk.WORKORDERID , wo.workorderid) "Module ID", 'Request' "Module", CASE WHEN MAX(tk.TASKID) IS NOT NULL THEN MAX(wo2.TITLE) ELSE MAX(wo.TITLE) END "Title",CAST(SUM(ct.TIMESPENT) AS FLOAT)/1000/3600 "Time Spent",to_timestamp(MAX(ct.createdtime)/1000)::TIMESTAMP "Time Spent Created Time", rctd.FIRST_NAME "Time Spent Technician" FROM ChargesTable ct 
LEFT JOIN WorkOrderToCharge wotoc ON ct.CHARGEID=wotoc.CHARGEID 
LEFT JOIN WorkOrder wo ON wotoc.WORKORDERID=wo.WORKORDERID 
LEFT JOIN TaskToCharge tkc ON ct.CHARGEID=tkc.CHARGEID 
LEFT JOIN  TaskDetails tk ON tkc.TASKID=tk.TASKID 
LEFT JOIN WorkorderToTaskDetails wtk ON tk.TASKID=wtk.TASKID 
LEFT JOIN Workorder wo2 ON wtk.WORKORDERID=wo2.WORKORDERID 
LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID 
WHERE (wtk.WORKORDERID IS NOT NULL OR wo.workorderid IS NOT NULL) AND ct.createdtime >= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2013-06-01 00:00:00') * 1000 AS BIGINT)  AND  ct.createdtime <= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2013-06-30 00:00:00') * 1000 AS BIGINT)  GROUP BY COALESCE(wtk.WORKORDERID , wo.workorderid), rctd.FIRST_NAME
UNION
SELECT COALESCE(ptk.PROBLEMID , pb.PROBLEMID) "Module ID",'Problem' "Module", CASE WHEN MAX(tk.TASKID) IS NOT NULL THEN MAX(pb2.TITLE) ELSE MAX(pb.TITLE) END "Title",  CAST
(SUM(ct.TIMESPENT) AS FLOAT)/1000/3600  "Time Spent",to_timestamp(MAX(ct.createdtime)/1000)::TIMESTAMP "Time Spent Created Time" ,rctd.FIRST_NAME "Time Spent Technician" FROM ChargesTable ct LEFT JOIN ProblemToCharge pbtoc ON ct.CHARGEID=pbtoc.CHARGEID 
LEFT JOIN Problem pb ON pbtoc.PROBLEMID=pb.PROBLEMID 
LEFT JOIN TaskToCharge tkc ON ct.CHARGEID=tkc.CHARGEID 
LEFT JOIN  TaskDetails tk ON tkc.TASKID=tk.TASKID 
LEFT JOIN ProblemToTaskDetails ptk ON tk.TASKID=ptk.TASKID 
LEFT JOIN Problem pb2 ON ptk.PROBLEMID=pb2.PROBLEMID 
LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID 
WHERE (ptk.PROBLEMID IS NOT NULL OR pb.PROBLEMID IS NOT NULL) AND ct.createdtime >= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2013-06-01 00:00:00') * 1000 AS BIGINT)  AND  ct.createdtime <= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2013-06-30 00:00:00') * 1000 AS BIGINT)  GROUP BY COALESCE(ptk.PROBLEMID , pb.PROBLEMID), rctd.FIRST_NAME
UNION
SELECT COALESCE(ctk.CHANGEID , ch.CHANGEID) "Module ID", 'Change' "Module",CASE WHEN MAX(tk.TASKID) IS NOT NULL THEN MAX(ch2.TITLE) ELSE MAX(ch.TITLE) END "Title",  CAST(SUM(ct.TIMESPENT) AS FLOAT)/1000/3600  "Time Spent", to_timestamp(MAX(ct.createdtime)/1000)::TIMESTAMP "Time Spent Created Time" ,rctd.FIRST_NAME "Time Spent Technician" 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 
WHERE (ctk.CHANGEID IS NOT NULL OR ch.CHANGEID IS NOT NULL) AND ct.createdtime >= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2013-06-01 00:00:00') * 1000 AS BIGINT)  AND  ct.createdtime <= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2013-06-30 00:00:00') * 1000 AS BIGINT) GROUP BY COALESCE(ctk.CHANGEID , ch.CHANGEID), rctd.FIRST_NAME
UNION
SELECT tpr.PROJECTID "Module ID",'Project' "Module" , MAX(pr.TITLE) "Title",  CAST(SUM(ct.TIMESPENT) AS FLOAT)/1000/3600  "Time Spent",to_timestamp(MAX(ct.createdtime)/1000)::TIMESTAMP "Time Spent Created Time" ,rctd.FIRST_NAME "Time Spent Technician" FROM ChargesTable ct 
LEFT JOIN TaskToCharge tkc ON ct.CHARGEID=tkc.CHARGEID 
LEFT JOIN  TaskDetails tk ON tkc.TASKID=tk.TASKID 
LEFT JOIN TaskTOProjects tpr ON tk.taskid=tpr.taskid 
LEFT JOIN ProjectDetails pr ON tpr.PROJECTID=pr.PROJECTID 
LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID WHERE  tk.MODULE IN ('project', 'milestone') AND ct.createdtime >= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2013-06-01 00:00:00') * 1000 AS BIGINT)  AND  ct.createdtime <= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2013-06-30 00:00:00') * 1000 AS BIGINT)  GROUP BY tpr.PROJECTID,rctd.FIRST_NAME
UNION
SELECT tk.TASKID "Module ID", 'General' "Module" ,MAX(tk.TITLE) "Title", CAST(SUM(ct.TIMESPENT) AS FLOAT)/1000/3600  "Time Spent",to_timestamp(MAX(ct.createdtime)/1000)::TIMESTAMP "Time Spent Created Time" ,rctd.FIRST_NAME "Time Spent Technician" FROM ChargesTable ct 
LEFT JOIN TaskToCharge tkc ON ct.CHARGEID=tkc.CHARGEID 
LEFT JOIN  TaskDetails tk ON tkc.TASKID=tk.TASKID  
LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID 
where module='general' AND ct.createdtime >= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2013-06-01 00:00:00') * 1000 AS BIGINT)  AND  ct.createdtime <= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2013-06-30 00:00:00') * 1000 AS BIGINT)  GROUP BY tk.TASKID,rctd.FIRST_NAME ORDER BY 2
 
 
MSSQL:
 
SELECT COALESCE(wtk.WORKORDERID , wo.workorderid) "Module ID", 'Request' "Module", CASE WHEN MAX(tk.TASKID) IS NOT NULL THEN MAX(wo2.TITLE) ELSE MAX(wo.TITLE) END "Title", CAST(SUM(ct.TIMESPENT) AS FLOAT)/1000/3600 "Time Spent",dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (MAX(ct.CREATEDTIME)/1000),'1970-01-01 00:00:00') "Time Spent Created Time", rctd.FIRST_NAME "Time Spent Technician" FROM ChargesTable ct 
LEFT JOIN WorkOrderToCharge wotoc ON ct.CHARGEID=wotoc.CHARGEID 
LEFT JOIN WorkOrder wo ON wotoc.WORKORDERID=wo.WORKORDERID 
LEFT JOIN TaskToCharge tkc ON ct.CHARGEID=tkc.CHARGEID 
LEFT JOIN  TaskDetails tk ON tkc.TASKID=tk.TASKID 
LEFT JOIN WorkorderToTaskDetails wtk ON tk.TASKID=wtk.TASKID 
LEFT JOIN Workorder wo2 ON wtk.WORKORDERID=wo2.WORKORDERID 
LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID 
WHERE (wtk.WORKORDERID IS NOT NULL OR wo.workorderid IS NOT NULL) AND dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (ct.CREATEDTIME/1000),'1970-01-01 00:00:00') >= convert(varchar,'2013-06-01 00:00',21) and dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (ct.CREATEDTIME/1000),'1970-01-01 00:00:00') <= convert(varchar,'2013-06-30 23:59',21) GROUP BY COALESCE(wtk.WORKORDERID , wo.workorderid), rctd.FIRST_NAME
UNION
SELECT COALESCE(ptk.PROBLEMID , pb.PROBLEMID) "Module ID",'Problem' "Module", CASE WHEN MAX(tk.TASKID) IS NOT NULL THEN MAX(pb2.TITLE) ELSE MAX(pb.TITLE) END "Title", CAST(SUM(ct.TIMESPENT) AS FLOAT)/1000/3600 "Time Spent",dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (MAX(ct.CREATEDTIME)/1000),'1970-01-01 00:00:00') "Time Spent Created Time",rctd.FIRST_NAME "Time Spent Technician" FROM ChargesTable ct 
LEFT JOIN ProblemToCharge pbtoc ON ct.CHARGEID=pbtoc.CHARGEID 
LEFT JOIN Problem pb ON pbtoc.PROBLEMID=pb.PROBLEMID 
LEFT JOIN TaskToCharge tkc ON ct.CHARGEID=tkc.CHARGEID 
LEFT JOIN  TaskDetails tk ON tkc.TASKID=tk.TASKID 
LEFT JOIN ProblemToTaskDetails ptk ON tk.TASKID=ptk.TASKID 
LEFT JOIN Problem pb2 ON ptk.PROBLEMID=pb2.PROBLEMID 
LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID 
WHERE (ptk.PROBLEMID IS NOT NULL OR pb.PROBLEMID IS NOT NULL) AND dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (ct.CREATEDTIME/1000),'1970-01-01 00:00:00') >= convert(varchar,'2013-06-01 00:00',21) and dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (ct.CREATEDTIME/1000),'1970-01-01 00:00:00') <= convert(varchar,'2013-06-30 23:59',21) GROUP BY COALESCE(ptk.PROBLEMID , pb.PROBLEMID), rctd.FIRST_NAME
UNION
SELECT COALESCE(ctk.CHANGEID , ch.CHANGEID) "Module ID", 'Change' "Module",CASE WHEN MAX(tk.TASKID) IS NOT NULL THEN MAX(ch2.TITLE) ELSE MAX(ch.TITLE) END "Title", CAST(SUM(ct.TIMESPENT) AS FLOAT)/1000/3600 "Time Spent",dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (MAX(ct.CREATEDTIME)/1000),'1970-01-01 00:00:00') "Time Spent Created Time",rctd.FIRST_NAME "Time Spent Technician" 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 
WHERE (ctk.CHANGEID IS NOT NULL OR ch.CHANGEID IS NOT NULL) AND dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (ct.CREATEDTIME/1000),'1970-01-01 00:00:00') >= convert(varchar,'2013-06-01 00:00',21) and dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (ct.CREATEDTIME/1000),'1970-01-01 00:00:00') <= convert(varchar,'2013-06-30 23:59',21) GROUP BY COALESCE(ctk.CHANGEID , ch.CHANGEID), rctd.FIRST_NAME
UNION
SELECT tpr.PROJECTID "Module ID",'Project' "Module" , MAX(pr.TITLE) "Title", CAST(SUM(ct.TIMESPENT) AS FLOAT)/1000/3600
 "Time Spent",dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (MAX(ct.CREATEDTIME)/1000),'1970-01-01 00:00:00') "Time Spent Created Time",rctd.FIRST_NAME "Time Spent Technician" FROM ChargesTable ct 
LEFT JOIN TaskToCharge tkc ON ct.CHARGEID=tkc.CHARGEID 
LEFT JOIN  TaskDetails tk ON tkc.TASKID=tk.TASKID 
LEFT JOIN TaskTOProjects tpr ON tk.taskid=tpr.taskid 
LEFT JOIN ProjectDetails pr ON tpr.PROJECTID=pr.PROJECTID 
LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID WHERE tk.MODULE IN ('project', 'milestone') AND dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (ct.CREATEDTIME/1000),'1970-01-01 00:00:00') >= convert(varchar,'2013-06-01 00:00',21) and dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (ct.CREATEDTIME/1000),'1970-01-01 00:00:00') <= convert(varchar,'2013-06-30 23:59',21) GROUP BY tpr.PROJECTID,rctd.FIRST_NAME
UNION
SELECT tk.TASKID "Module ID", 'General' "Module" ,MAX(tk.TITLE) "Title", CAST(SUM(ct.TIMESPENT) AS FLOAT)/1000/3600 "Time Spent",dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (MAX(ct.CREATEDTIME)/1000),'1970-01-01 00:00:00') "Time Spent Created Time",rctd.FIRST_NAME "Time Spent Technician" FROM ChargesTable ct 
LEFT JOIN TaskToCharge tkc ON ct.CHARGEID=tkc.CHARGEID 
LEFT JOIN  TaskDetails tk ON tkc.TASKID=tk.TASKID  
LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID 
where module='general' AND dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (ct.CREATEDTIME/1000),'1970-01-01 00:00:00') >= convert(varchar,'2013-06-01 00:00',21) and dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (ct.CREATEDTIME/1000),'1970-01-01 00:00:00') <= convert(varchar,'2013-06-30 23:59',21)  GROUP BY tk.TASKID,rctd.FIRST_NAME ORDER BY 2
 
 
MYSQL:
 
SELECT COALESCE(wtk.WORKORDERID , wo.workorderid) "Module ID", 'Request' "Module", CASE WHEN MAX(tk.TASKID) IS NOT NULL THEN MAX(wo2.TITLE) ELSE MAX(wo.TITLE) END "Title", SUM(ct.TIMESPENT)/1000/3600
 "Time Spent",from_unixtime(MAX(ct.CREATEDTIME/1000)) "Time Spent Created Time", rctd.FIRST_NAME "Time Spent Technician" FROM ChargesTable ct
LEFT JOIN WorkOrderToCharge wotoc ON ct.CHARGEID=wotoc.CHARGEID
LEFT JOIN WorkOrder wo ON wotoc.WORKORDERID=wo.WORKORDERID
LEFT JOIN TaskToCharge tkc ON ct.CHARGEID=tkc.CHARGEID
LEFT JOIN  TaskDetails tk ON tkc.TASKID=tk.TASKID
LEFT JOIN WorkorderToTaskDetails wtk ON tk.TASKID=wtk.TASKID
LEFT JOIN Workorder wo2 ON wtk.WORKORDERID=wo2.WORKORDERID
LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID
WHERE (wtk.WORKORDERID IS NOT NULL OR wo.workorderid IS NOT NULL) AND (ct.CREATEDTIME >= unix_timestamp('2013-06-01 00:00:00')*1000)  AND (ct.CREATEDTIME <= unix_timestamp('2013-06-30 23:59:59')*1000)   GROUP BY COALESCE(wtk.WORKORDERID , wo.workorderid), rctd.FIRST_NAME
UNION
SELECT COALESCE(ptk.PROBLEMID , pb.PROBLEMID) "Module ID",'Problem' "Module", CASE WHEN MAX(tk.TASKID) IS NOT NULL THEN MAX(pb2.TITLE) ELSE MAX(pb.TITLE) END "Title",  SUM(ct.TIMESPENT)/1000/3600  "Time Spent",from_unixtime(MAX(ct.CREATEDTIME/1000)) "Time Spent Created Time",rctd.FIRST_NAME "Time Spent Technician" FROM ChargesTable ct
LEFT JOIN ProblemToCharge pbtoc ON ct.CHARGEID=pbtoc.CHARGEID
LEFT JOIN Problem pb ON pbtoc.PROBLEMID=pb.PROBLEMID
LEFT JOIN TaskToCharge tkc ON ct.CHARGEID=tkc.CHARGEID
LEFT JOIN  TaskDetails tk ON tkc.TASKID=tk.TASKID
LEFT JOIN ProblemToTaskDetails ptk ON tk.TASKID=ptk.TASKID
LEFT JOIN Problem pb2 ON ptk.PROBLEMID=pb2.PROBLEMID
LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID
WHERE (ptk.PROBLEMID IS NOT NULL OR pb.PROBLEMID IS NOT NULL) AND (ct.CREATEDTIME >= unix_timestamp('2013-06-01 00:00:00')*1000)  AND (ct.CREATEDTIME <= unix_timestamp('2013-06-30 23:59:59')*1000)   GROUP BY COALESCE(ptk.PROBLEMID , pb.PROBLEMID), rctd.FIRST_NAME
UNION
SELECT COALESCE(ctk.CHANGEID , ch.CHANGEID) "Module ID", 'Change' "Module",CASE WHEN MAX(tk.TASKID) IS NOT NULL THEN MAX(ch2.TITLE) ELSE MAX(ch.TITLE) END "Title",  SUM(ct.TIMESPENT)/1000/3600  "Time Spent",from_unixtime(MAX(ct.CREATEDTIME/1000)) "Time Spent Created Time",rctd.FIRST_NAME "Time Spent Technician" 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
WHERE (ctk.CHANGEID IS NOT NULL OR ch.CHANGEID IS NOT NULL) AND (ct.CREATEDTIME >= unix_timestamp('2013-06-01 00:00:00')*1000)  AND (ct.CREATEDTIME <= unix_timestamp('2013-06-30 23:59:59')*1000)  GROUP BY COALESCE(ctk.CHANGEID , ch.CHANGEID), rctd.FIRST_NAME
UNION
SELECT tpr.PROJECTID "Module ID",'Project' "Module" , MAX(pr.TITLE) "Title",  SUM(ct.TIMESPENT)/1000/3600  "Time Spent", from_unixtime(MAX(ct.CREATEDTIME/1000)) "Time Spent Created Time",rctd.FIRST_NAME "Time Spent Technician" FROM ChargesTable ct
LEFT JOIN TaskToCharge tkc ON ct.CHARGEID=tkc.CHARGEID
LEFT JOIN  TaskDetails tk ON tkc.TASKID=tk.TASKID
LEFT JOIN TaskTOProjects tpr ON tk.taskid=tpr.taskid
LEFT JOIN ProjectDetails pr ON tpr.PROJECTID=pr.PROJECTID
LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID WHERE tk.MODULE IN ('project', 'milestone') AND (ct.CREATEDTIME >= unix_timestamp('2013-06-01 00:00:00')*1000)  AND (ct.CREATEDTIME <= unix_timestamp('2013-06-30 23:59:59')*1000)   GROUP BY tpr.PROJECTID,rctd.FIRST_NAME
UNION
SELECT tk.TASKID "Module ID", 'General' "Module" ,MAX(tk.TITLE) "Title", SUM(ct.TIMESPENT)/1000/3600  "Time Spent",from_unixtime(MAX(ct.CREATEDTIME/1000)) "Time Spent Created Time",rctd.FIRST_NAME "Time Spent Technician" FROM ChargesTable ct
LEFT JOIN TaskToCharge tkc ON ct.CHARGEID=tkc.CHARGEID
LEFT JOIN  TaskDetails tk ON tkc.TASKID=tk.TASKID 
LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID
where module='general' AND (ct.CREATEDTIME >= unix_timestamp('2013-06-01 00:00:00')*1000)  AND (ct.CREATEDTIME <= unix_timestamp('2013-06-30 23:59:59')*1000)  GROUP BY tk.TASKID,rctd.FIRST_NAME ORDER BY 2


 

Note : Login to ServiceDesk Plus, go to Reports tab > New Query Report > Copy the query to the query editor and run the report. 







      New to M365 Manager Plus?
      New to M365 Manager Plus?
        New to RecoveryManager Plus?
        New to RecoveryManager Plus?
          New to Exchange Reporter Plus?
          New to Exchange Reporter Plus?
            New to SharePoint Manager Plus?
            New to SharePoint Manager Plus?
                See all integrations
                New to ADManager Plus?
                See all integrations
                New to ADManager Plus?

                  New to ADSelfService Plus?

                  Start your free trial
                  Start your free trial

                    • Related Articles

                    • Project Time spent

                      MSSQL SELECT pr.ProjectID "Project ID", pr.TITLE "Project Title", taskdet.TaskID "TaskID", taskdet.TITLE "Title", tkd.description "Task Description", taskowner.FIRST_NAME "Owner", dateadd(s, datediff(s, GETUTCDATE(), getdate()) + ...

                    • Problem time spent

                      SELECT prob.PROBLEMID "Problem ID", prob.TITLE "Title", catadef.CATEGORYNAME "Category", LONGTODATE(prob.REPORTEDTIME) "Reported Date", ownaaa.FIRST_NAME "Technician", priodef.PRIORITYNAME "Priority", statdef.STATUSNAME "Status", ...

                    • Query to get total worklog time spent on a request. (PGSQL)

                      Tested in build PGSQL (14300) 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. ...

                    • Timespent report across modules (Request, Problem, Change) (MSSQL)

                      Tested in build MSSQL (14306) SELECT ad.ORG_NAME AS "Account",COALESCE(wtk.WORKORDERID , wo.workorderid) "Module ID", Convert(varchar(10),CONVERT(date,(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + ((ct.CREATEDTIME)/1000),'1970-01-01 ...

                    • Query to show tickets that shows total time spent of the ticket more than x hours (MSSQL)

                      Tested in MSSQL build (14306) 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", ...

                      Related Products