I would like to compute how many minutes or hours the employee left work by comparing two records

[YoBi4Ever]

I have a database where each record contains IN and OUT. I would like to see how left for more than 20 minutes.
How can i check the OUT time in the first record with the IN time and then calculate the minutes?
Taking into considerations there are other days in the DB and i want to do it for each day seperate

InTime	OutTime	Total minutes OUT ( i calculated this manually)
2024-12-16 06:11:07	2024-12-16 11:30:57
2024-12-16 11:49:30	2024-12-16 12:19:45	11:30:57 to 11:49:30 = 00:19:27 (hh:mm:ss)
2024-12-16 12:33:58	2024-12-16 15:03:20	12:19:45 to 12:33:58 = 00:14:13 (hh:mm:ss)

 

[zazzi]

Examples:

? ALLTRIM(STR(CTOT('21/01/2025 18:00:00') - CTOT('21/01/2025 17:00:00'))) + ' seconds'
? ALLTRIM(STR((CTOT('21/01/2025 18:00:00') - CTOT('21/01/2025 17:00:00')) / 60)) + ' minutes'
? ALLTRIM(STR((CTOT('21/01/2025 18:00:00') - CTOT('21/01/2025 17:00:00')) / 60 / 60)) + ' hours'

Adjust based on your SET DATE setting

 

[Andrzejek]

What you are talking about is a Duration between two dates, which you can calculate by using DateDiff Function
So, if you want to know the duration in Minutes ("n") between 2024-12-16 11:30:57 and 2024-12-16 11:49:30, you can do:

Dim datStart As Date
Dim datStop As Date

datStart = CDate("2024-12-16 11:30:57")
datStop = CDate("2024-12-16 11:49:30")
MsgBox DateDiff("n", datStart, datStop)

 

[dhookom]

You will need a subquery or Domain function to calculate across records. This will give you the number of minutes (and fractions). You can do other calculations but the key is the subquery. I don't this this result can be edited which would require a domain function.

SELECT tblInOut.InTime, tblInOut.OutTime,
DateDiff("s",(SELECT Max(OutTIme) FROM tblInOut O WHERE O.OutTime < tblInOut.InTime),[inTime])/60 AS TotalMinOut
FROM tblInOut;