Query working abnormally

Question

why does my query behaves abnormally ? i have worked a lot to make it working, i did somehow to make it work via stackoverflow posts etc but still it has some problems

I am trying to calculate total work hours of a person like by finding difference between Intime and Outtime , it works but for some records it shows wrong hours

e.g.

InTime      TimeOut       total time
12:00       18:11            5:11 // wrong, it should be 6:11
13:01       18:20            4:19 // Wrong,
09:33       17:15            7:42 // It fine and correct

Like for many records it works correctly but for some it doesn't, always shows -1 hours of actual.

WITH times
        AS (
            SELECT t1.EmplID
                ,t3.EmplName
                ,min(t1.RecTime) AS InTime
                ,max(t2.RecTime) AS [TimeOut]
                ,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
            GROUP BY t1.EmplID
                ,t3.EmplName
                ,t1.RecDate
            )
        SELECT EmplID
            ,EmplName
            ,InTime
            ,[TimeOut]
            ,[DateVisited]
            ,CASE 
                WHEN minpart = 0
                    THEN CAST(hourpart AS NVARCHAR(200)) + ':00'
                ELSE CAST((hourpart - 1) AS NVARCHAR(200)) + ':' + CAST(minpart AS NVARCHAR(200))
                END AS 'total time'
        FROM (
            SELECT EmplID
                ,EmplName
                ,InTime
                ,[TimeOut]
                ,[DateVisited]
                ,DATEDIFF(Hour, InTime, [TimeOut]) AS hourpart
                ,DATEDIFF(minute, InTime, [TimeOut]) % 60 AS minpart
            FROM times
            ) source

This post almost made it working but later i found out problems. DateDiff to output hours and minutes

Example SQLFiddle

Output:

EmplID             EmpName  InTime  TimeOut DateVisited   Total time
00000024            Tariq   09:59   18:56   2013-09-21  8:57
00000024            Tariq   10:57   19:00   2013-09-23  8:3
00000024            Tariq   11:40   18:58   2013-09-24  7:18

Answer 1

just take the minutes and as datediff and then make a new time out of that

with times as (
SELECT    t1.EmplID
        , t3.EmplName
        , min(t1.RecTime) AS InTime
        , max(t2.RecTime) AS [TimeOut]
        , t1.RecDate AS [DateVisited]
        , DATEDIFF(minute,min(t1.RecTime), max(t2.RecTime)) TotalMin
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 
group by 
          t1.EmplID
        , t3.EmplName
        , t1.RecDate
)
SELECT    EmplID
        , EmplName
        , InTime
        , [TimeOut]
        , [DateVisited]
        , CAST((TotalMin / 60) as varchar(2)) + ':' + CAST((TotalMin % 60) as varchar(2)) as [total time]
from times
Order By EmplID, DateVisited 

Answer 2

SELECT EmplID
                ,EmplName
                ,InTime
                ,[TimeOut]
                ,[DateVisited]
                ,CASE 
                    WHEN minpart = 0
                        THEN CAST(hourpart AS NVARCHAR(200)) + ':00'
                    WHEN minpart <10
                        THEN CAST(hourpart AS NVARCHAR(200)) + ':0'+ CAST(minpart AS NVARCHAR(200))
                    ELSE CAST(hourpart AS NVARCHAR(200)) + ':' + CAST(minpart AS NVARCHAR(200))

END AS 'total time'
            FROM (
                SELECT EmplID
                    ,EmplName
                    ,InTime
                    ,[TimeOut]
                    ,[DateVisited]
                    ,DATEDIFF(minute, InTime, [TimeOut])/60 AS hourpart
                    ,DATEDIFF(minute, InTime, [TimeOut]) % 60 AS minpart
                FROM times
                ) source