0

This query shows emplName,emplID, totalworking time, InTime, OutTime, DateVisited, Overtime for an employee based on his InTime and Outime, that's OK. Now i am trying to modify it to show only emplID, EmplName, Total Working hours(Per month), total overtime (per month).

e.g.

Empid   EmplName  TotalWorkingHours TotalOvertime  Month
00001   John      77:00               05:55       2013-02
00002   Masn      57:00               04:56       2013-02

Query:

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
)
SELECT 
 EmplID
,EmplName
,ShiftId As ShiftID
,InTime
,[TimeOut]
,convert(char(5),cast([TimeOutSub] - InTimeSub as time), 108) 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 order by EmplID, ShiftID, DateVisited
1
Contributor
1
Reply
10
Views
3 Years
Discussion Span
Last Post by haymen.roth.3
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.