0

Successfully calculated TotalTime a person has worked and overtime he has done if total working time is greater than 08:00 but if not greater than it puts duration of time person has not worked in Overtime column BUT NOW overtime column should display that time in proper negative format e.g. if person has worked from 14:49 to
15:20 hours than Overtime column should show -00:29 but my column is displaying it like this 23:29, why 23 ?

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 cast(8 - (TimeOutSub - InTimeSub) as time) end, 108) as overtime
FROM times

OUTPUT:

Attachments query.png 15.87 KB
1
Contributor
2
Replies
11
Views
3 Years
Discussion Span
Last Post by haymen.roth.3
0

i have solved it, all you daniweb losers, never reply

WITH Times AS
(   SELECT  emp.EmplID, 
            emp.EmplName,
            InTime = MIN(atd.RecTime),
            OutTime = MAX(atd.RecTime),
            TimeWorked = DATEDIFF(MINUTE, MIN(atd.RecTime), MAX(atd.RecTime)),
            OverTime = DATEDIFF(MINUTE, MIN(atd.RecTime), MAX(atd.RecTime)) - 480,
            [DateVisited] = atd.RecDate
    FROM    AtdRecord atd 
            INNER JOIN HrEmployee emp 
                ON atd.EmplID = emp.EmplID 
    GROUP BY emp.EmplID, emp.EmplName, atd.RecDate
    HAVING COUNT(atd.RecTime) > 1
)
SELECT  t.EmplID,
        t.EmplName,
        t.InTime,
        t.OutTime,
        t.DateVisited,
        t.TimeWorked,
        OverTime,
        FormattedTimeWorked = CONVERT(CHAR(5), DATEADD(MINUTE, t.TimeWorked, 0), 8),
        FormattedOverTime = CASE WHEN t.OverTime < 0 THEN '-' ELSE '' END +
                                CONVERT(CHAR(5), DATEADD(MINUTE, ABS(t.OverTime), 0), 8)
FROM    Times t;
This question has already been answered. 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.