I need to select total hours in one day. first-in and last-out. using SQL query

Question

how to code a query with this problem. I need to select total hours within a day. from first-in and Last-out then to sum again The period date_01 to date_16 which is 15days.

[EmployeeID]   [Att_Date]
 01            2022-02-01 07:39:12
 01            2022-02-01 19:39:12
 01            2022-02-02 08:39:12
 01            2022-02-02 19:00:12
 01            2022-02-03 08:00:12
 01            2022-02-03 19:00:12
 02            2022-02-02 08:00:12
 02            2022-02-02 19:39:12... (Raj Marvic)
 

I tried this code below, but it just only sum result total hours from first-indate to Last-out date, which is its wrong. I need to total daily first, to sum the total hours from first-indate and Last-outDate.

SELECT EmployeeID, 
       TIMESTAMPDIFF(hour, MIN(Att_Date),MAX(Att_Date)) AS diff_in_hours
FROM att_details 
GROUP BY EmployeeID;

Answer 1

I think the reason you are missing your correct total is because the MIN() and MAX() are getting the earliest date/time vs latest date/time which span multiple days. What I think you are trying to get is the aggregation of EACH DAY, such as totaled over a given week, or multiple day span. That said, your first query should be grouped by employee AND Date (without time context), THEN sum that result. One caveat not mentioned... Is it possible for one employee to clock-in/out multiple times in a single day, such as to account for lunch time where that time is excluded from the total work day? If so, that will need to be accounted for.

select
      EmployeeID,
      Sum( DailyHours ) TotalHours
   from
      ( SELECT 
              EmployeeID, 
              Date( Att_Date ) SingleDay,
              TIMESTAMPDIFF(hour, MIN(Att_Date),MAX(Att_Date)) AS DailyHours
           FROM 
              att_details 
           GROUP BY 
              EmployeeID,
              Date( Att_Date ) ) PQ
   group by
      EmployeeID

Answer 2

Using aggregation along with DATEDIFF we can try:

SELECT
    [Employee ID],
    DATEDIFF(hour, MIN([Date/time]), MAX([Date/time])) AS diff_in_hours
FROM yourTable
GROUP BY
    [Employee ID];