I have created a scalar function and converted the seconds to HHMMSS. Then include the function in the main query.
ALTER function [dbo].[ConvertSecondsToHHMMSS]
(
@TotalSeconds int
)
Returns nvarchar(20)
As
Begin
declare @hours int, @minutes int, @seconds int, @result nvarchar(20)
set @hours = @TotalSeconds / 3600
set @minutes = (@TotalSeconds % 3600) / 60
set @seconds = @TotalSeconds % 60
set @result = CONVERT(nvarchar(20),@hours) + ':' + CONVERT(nvarchar(20),@minutes) + ':' +CONVERT(nvarchar(20),@seconds)
return @result
end
Main Query:
set @query= 'select UserName, '+@cols+' from
(
select e.UserName, c._Date, dbo.ConvertSecondsToHHMMSS(SUM(DATEDIFF(SS,0,c.TimeTaken))) As TimeTaken from MIS_BM_Users e
inner join MIS_Opus c on e.EmpId=c.EmpId
where (e.AccountManagerID='''+@AccountManagerId+''')
and c.Category not in ('''+@Condition1+''', '''+@condition2+''')
group by c._Date, e.UserName
) As SourceTable
Pivot
(
MIN(TimeTaken) for _Date in ('+@cols+')
) As Pvt'
execute(@query)