Nicolas Nicolas - 2 months ago 6
SQL Question

Calculating hours for a working day spread over 2 separate days - Access SQL

For a company I am calculating the worked hours for each employee. The calculation is done by them clocking in- and out.

So it gets inserted in the database as follows:

--Record

Relation: 1101
Clock Date and time: 2016-09-05 14:00
Type: Clock IN
Worked hours: 0 (always 0 on IN)

--Record

Relation: 1101
Clock Date and time: 2016-09-05 16:00
Type: Clock OUT
Worked hours: 2 (= clocked out datetime minus clocked in datetime)


This always worked untill recently. The company is extended and they now work untill +/- 1 a clock in the night (1:00) while they used to work till max 10 a clock in the evenening (22:00).

In my Access database I am displaying a form that shows total worked hours for a whole day instead of worked hours for each clock in and out. I calculate these hours based on a Min(date) and Max(date) for the specific employee. However, because they sometime now clock OUT in the night, the records get messed up.

For example: Employee A clocks in at 17:00 on 2016-09-05 and works untill 1:00 on 2016-09-06. The MIN gets the 17:00 time but the MAX now also get the 17:00 time (because the MIN/MAX are based on dates and the clocking out is on the Next day..). Because of this the Form is retrieving incorrect values.

Is there an easy way to build something around this?

The query looks like this:

SELECT EmpoyeeID
, EmployeeName
, EmployeeAge
, DatePart("yyyy", [ClockDateTime]) AS year
, DatePart("m", [ClockDateTime]) AS month
, DatePart("d", [ClockDateTime]) AS day
, Min(ClockDateTime) AS StartTime
, Max(ClockDateTime) AS EndTime
, DateDiff("n", Min([ClockDateTime]), Max([ClockDateTime]))/60 AS TotalWorkedToday
FROM HourRegistration
GROUP
BY EmpoyeeID
, EmployeeName
, EmployeeAge
, DatePart("yyyy", [ClockDateTime])
, DatePart("m", [ClockDateTime])
, DatePart("d", [ClockDateTime]);

Answer

You can use a correlated subquery to match up the start and end date/times for each shift. If your [HourRegistration] table contained

EmployeeID  ClockDateTime        Type
----------  -------------------  ----
      1101  2016-09-04 09:00:00  IN  
      1101  2016-09-04 17:00:00  OUT 
      1102  2016-09-05 10:00:00  IN  
      1102  2016-09-05 15:00:00  OUT 
      1101  2016-09-05 17:00:00  IN  
      1101  2016-09-06 01:00:00  OUT 
      1101  2016-09-06 18:00:00  IN  
      1101  2016-09-07 01:00:00  OUT 

then you could create a saved query named [ShiftStartAndEnd] with the code ...

SELECT 
    t1.EmployeeID, 
    t1.ClockDateTime AS StartDateTime, 
    (
        SELECT MIN(t2.ClockDateTime)
        FROM HourRegistration t2
        WHERE t2.EmployeeID = t1.EmployeeID
            AND t2.ClockDateTime > t1.ClockDateTime
            AND t2.Type='OUT'
    ) AS EndDateTime
FROM HourRegistration AS t1
WHERE t1.Type='IN';

... that returns ...

EmployeeID  StartDateTime        EndDateTime        
----------  -------------------  -------------------
      1101  2016-09-04 09:00:00  2016-09-04 17:00:00
      1101  2016-09-05 17:00:00  2016-09-06 01:00:00
      1101  2016-09-06 18:00:00  2016-09-07 01:00:00
      1102  2016-09-05 10:00:00  2016-09-05 15:00:00

Then you could use that saved query as the basis for another query that calculates the correct shift length using DateDiff(), similar to what you did before ...

SELECT
    EmployeeId,
    StartDateTime AS ShiftStart,
    EndDateTime AS ShiftEnd,
    DateDiff("n", StartDateTime, EndDateTime) / 60 AS ShiftHours
FROM ShiftStartAndEnd

... which returns

EmployeeId  ShiftStart           ShiftEnd             ShiftHours
----------  -------------------  -------------------  ----------
      1101  2016-09-04 09:00:00  2016-09-04 17:00:00           8
      1101  2016-09-05 17:00:00  2016-09-06 01:00:00           8
      1101  2016-09-06 18:00:00  2016-09-07 01:00:00           7
      1102  2016-09-05 10:00:00  2016-09-05 15:00:00           5
Comments