Hi ,

I have a query that shows time spent by technicians per day through worklogs.

How can I get a total of time spent by all these technicians combined per month?

We use tasks more than requests to log our time.

Here is my query:

SELECT tk.TASKID "Module ID" ,

 'General' "Module" ,

 MAX(tk.TITLE) "Title" ,

ct.DESCRIPTION "Description" ,

CAST(CAST(SUM(ct.TIMESPENT) / 1000 / 3600 AS VARCHAR) + ':'

+ CAST(CAST(ROUND(( ( ( CAST(SUM(ct.TIMESPENT) AS DECIMAL)

/ 3600000 ) % 1 ) * 60 ), 0) AS BIGINT) AS VARCHAR) AS TIME) AS "Time Spent" ,

DATEADD(s,

DATEDIFF(s, GETUTCDATE(), GETDATE())

+ ( MAX(ct.TS_STARTTIME) / 1000 ),

'01-01-1970 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.TS_STARTTIME / 1000 ),

'01-01-1970 00:00:00') >= CONVERT(VARCHAR, @start_date, 21)

AND DATEADD(s,

DATEDIFF(s, GETUTCDATE(), GETDATE())

+ ( ct.TS_STARTTIME / 1000 ),

'01-01-1970 00:00:00') <= CONVERT(VARCHAR, @end_date, 21)

AND rctd.FIRST_NAME IN (

SELECT ISNULL(Value, 99)

FROM [dbo].[SplitMultivaluedString](@technician, ',') )

GROUP BY tk.TASKID ,

rctd.FIRST_NAME ,

ct.DESCRIPTION ,

ct.timespent

Thanks You