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,

Is OverTime a varchar column? If so, you need to cast it to a type that can be summed.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.