Member Avatar for haymen.roth.3

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
  • 177 Views
  • 1 Hour Discussion Span
  • Latest Post Latest Post by haymen.roth.3

Dani AI

Generated

— The simplest reliable approach is to convert each day’s In/Out pair to a single “worked minutes” value, compute any daily overtime against the employee’s shift end (taking overnight shifts into account), then aggregate those minutes by year-month and format back to hh:mm. The MIN/MAX per date method works only when there is exactly one meaningful In and one Out per day; multiple punches or missing outs need special handling.

Recommended steps (conceptually):

  • produce a day-level row with InDateTime and OutDateTime and calculate WorkMinutes = DATEDIFF(MINUTE,...).
  • build the shift-end datetime from RecDate + shift.EndAtdTime and add one day when EndAtdTime < StAtdTime (overnight shifts), then compute OTMinutes = positive difference between OutDateTime and shift-end.
  • aggregate by CONVERT(char(7), RecDate, 120) (gives "yyyy-mm") and SUM minutes.
  • format totals with integer division and modulo to get hh:mm for display.

A compact example (adapt to exact column types in the schema):

WITH DailyTotals AS (
  SELECT r.EmplID, e.EmplName, r.RecDate,
         DATEADD(SECOND, DATEDIFF(SECOND, 0, MIN(r.RecTime)), CAST(r.RecDate AS datetime)) AS InDateTime,
         DATEADD(SECOND, DATEDIFF(SECOND, 0, MAX(r.RecTime)), CAST(r.RecDate AS datetime)) AS OutDateTime,
         DATEDIFF(MINUTE,
           DATEADD(SECOND, DATEDIFF(SECOND, 0, MIN(r.RecTime)), CAST(r.RecDate AS datetime)),
           DATEADD(SECOND, DATEDIFF(SECOND, 0, MAX(r.RecTime)), CAST(r.RecDate AS datetime))
         ) AS WorkMins,
         CASE WHEN DATEADD(SECOND, DATEDIFF(SECOND, 0, MAX(r.RecTime)), CAST(r.RecDate AS datetime)) >
                   DATEADD(SECOND, DATEDIFF(SECOND, 0, s.EndAtdTime),
                     DATEADD(DAY, CASE WHEN s.EndAtdTime < s.StAtdTime THEN 1 ELSE 0 END, CAST(r.RecDate AS datetime)))
              THEN DATEDIFF(MINUTE,
                   DATEADD(SECOND, DATEDIFF(SECOND, 0, s.EndAtdTime),
                     DATEADD(DAY, CASE WHEN s.EndAtdTime < s.StAtdTime THEN 1 ELSE 0 END, CAST(r.RecDate AS datetime))),
                   DATEADD(SECOND, DATEDIFF(SECOND, 0, MAX(r.RecTime)), CAST(r.RecDate AS datetime))
              ) ELSE 0 END AS OTMinutes
  FROM AtdRecord r
  JOIN HrEmployee e ON e.EmplID = r.EmplID
  JOIN AtdShiftSect s ON s.ShiftId = e.ShiftId
  GROUP BY r.EmplID, e.EmplName, r.RecDate, s.StAtdTime, s.EndAtdTime
),
Monthly AS (
  SELECT EmplID, EmplName, CONVERT(char(7), RecDate, 120) AS [Month],
         SUM(WorkMins) AS TotalWorkMins, SUM(OTMinutes) AS TotalOTMins
  FROM DailyTotals
  GROUP BY EmplID, EmplName, CONVERT(char(7), RecDate, 120)
)
SELECT EmplID, EmplName, [Month],
       CAST(TotalWorkMins/60 AS varchar(10)) + ':' + RIGHT('00' + CAST(TotalWorkMins%60 AS varchar(2)),2) AS TotalWorkingHours,
       CAST(TotalOTMins/60 AS varchar(10)) + ':' + RIGHT('00' + CAST(TotalOTMins%60 AS varchar(2)),2) AS TotalOvertime
FROM Monthly
ORDER BY EmplID, [Month];

Notes and troubleshooting:

  • If multiple in/out pairs per day exist, the MIN/MAX method undercounts; the correct approach is to pair rows (ROW_NUMBER/LEAD) and sum each in->out segment.
  • Treat missing OutTime as incomplete (NULL) and decide whether to exclude or flag such days.
  • Keep aggregation in minutes to avoid time arithmetic pitfalls, and ensure indexes on (EmplID, RecDate, RecTime) for performance.
Member Avatar for haymen.roth.3

any help plz >

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.