this query was working fine but when added OverTime part then it started throwing error:
Msg 8117, Level 16, State 1, Line 52
Operand data type varchar is invalid for sum operator.
with times as (
SELECT t1.EmplID
, t3.EmplName
, min(t1.RecTime) AS InTime
, max(t2.RecTime) AS [TimeOut]
, t4.ShiftId as ShiftID
, t4.StAtdTime as ShStartTime
, t4.EndAtdTime as ShEndTime
, cast(min(t1.RecTime) as datetime) AS InTimeSub
, cast(max(t2.RecTime) as datetime) AS TimeOutSub
, t1.RecDate AS [DateVisited]
FROM AtdRecord t1
INNER JOIN
AtdRecord t2
ON t1.EmplID = t2.EmplID
AND t1.RecDate = t2.RecDate
AND t1.RecTime < t2.RecTime
inner join
HrEmployee t3
ON t3.EmplID = t1.EmplID
inner join AtdShiftSect t4
ON t3.ShiftId = t4.ShiftId
group by
t1.EmplID
, t3.EmplName
, t1.RecDate
, t4.ShiftId
, t4.StAtdTime
, t4.EndAtdTime
),
CTE_FINAL as
(
SELECT
EmplID
,EmplName
,ShiftId As ShiftID
,InTime
,[TimeOut]
,datediff(minute,InTimeSub,[TimeOutSub]) TotalWorkingTime
,[DateVisited]
,CASE WHEN [InTime] IS NOT NULL AND [TimeOut] IS NOT NULL THEN
CONVERT(char(5),CASE WHEN CAST([TimeOutSub] AS DATETIME) >= ShEndTime And ShiftID = 'S002' Then LEFT(CONVERT(varchar(12), DATEADD(ms, DATEDIFF(ms, CAST(ShEndTime AS DATETIME),CAST([TimeOutSub] AS DATETIME)),0), 108),5)
WHEN CAST([TimeOutSub] AS DATETIME) >= ShEndTime And ShiftID = 'S001' Then LEFT(CONVERT(varchar(12), DATEADD(ms, DATEDIFF(ms, CAST(ShEndTime AS DATETIME), CAST([TimeOutSub] AS DATETIME)),0), 108),5)
ELSE '00:00' END, 108)
ELSE 'ABSENT' END AS OverTime
FROM times
)
select EmplID
,EmplName, OverTime,
cast (Sum(TotalWorkingTime)/60 as varchar(10))+':'+right('00'+cast(Sum(TotalWorkingTime)%60 as varchar(2)),2) as OverallTime,
cast (Sum(OverTime)/60 as varchar(10))+':'+right('00'+cast(Sum(OverTime)%60 as varchar(2)),2) as OverTime,
convert(varchar(4),year(Datevisited))+'-'+convert(varchar(3),Datename( Month,Datevisited)) as Month
from cte_final
group by EmplID, EmplName, convert(varchar(4),year(Datevisited))+'-'+convert(varchar(3),Datename( Month,Datevisited)), OverTime
order by EmplID, convert(varchar(4),year(Datevisited))+'-'+convert(varchar(3),Datename( Month,Datevisited))
the error is becasue of this part
cast (Sum(OverTime)/60 as varchar(10))+':'+right('00'+cast(Sum(OverTime)%60 as varchar(2)),2) as OverTime,
