0

i have wrote query to calculate TOTAL WORK TIME and Overtime of an employee based on Intime and outTime but can't figure out to put another column 'IsAbsent'. e.g. if person doesn't come on any specific date than his INtime and TimeOut would be empty then if both are empty then new column IsAbsent should contain ABSENT otherwise if both are filled than Present.

Query:

with times as (
SELECT    t1.EmplID
        , t3.EmplName
        , min(t1.RecTime) AS InTime
        , max(t2.RecTime) AS [TimeOut]
        , 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 
group by 
          t1.EmplID
        , t3.EmplName
        , t1.RecDate
)
SELECT EmplID
,EmplName
,InTime
,[TimeOut]
,[DateVisited]
,convert(char(5),cast([TimeOutSub] - InTimeSub as time), 108) totaltime
,convert(char(5), case when TimeOutSub - InTimeSub >= '08:01' then 
cast(TimeOutSub - dateadd(hour, 8, InTimeSub) as time) else '00:00' end, 108) as overtime
FROM times
3
Contributors
3
Replies
29
Views
3 Years
Discussion Span
Last Post by pritaeas
2

You can add:

CASE 
    WHEN INtime IS NULL AND TimeOut IS NULL THEN 'Absent'
    WHEN INtime IS NOT NULL AND TimeOut IS NOT NULL THEN 'Present'
    ELSE 'Unknown'
END AS IsAbsent
Votes + Comments
Sir please try to put in my code , i am getting errors
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.