CODEWITHSUNDEEP
sqldatetimems-access
Nicolas
Nicolas

Reputation: 2386

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]);

Upvotes: 1

Views: 899

Answers (2)

iDevlop
iDevlop

Reputation: 25272

Removing 2 hours from Start time and end time should do the trick.
Replace every [ClockDateTime] occurence in your query by [ClockDateTime] - #1/1/1900 2:0:0#

Removing 2 hours will set you back to the previous working situation, as long as the workday stays in the range 3am till 2am next day

Upvotes: 1

Gord Thompson
Gord Thompson

Reputation: 123829

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

Upvotes: 1

Related Questions