Time between records

[thmsjlmnt3953]

Hi,
I have a table which has a primary ID and then date/time then an empid field relating to an empid table,

Is it possible to design a query to return all records per empid which exceed 30 minutes for the last 30 days? or even 'today' or a specified date whicever is easiest?

 

[CJ_London]

I think what you are saying is you want to return all records where the difference in time between the current record and the previous record for the same empID is greater than 30 minutes.

Is this correct?

 

[thmsjlmnt3953]

Hi,
thats what im looking for, any difference between times per empid that exceed 30 mins - there should only be one per employee per day.

 

[CJ_London]

Your code will be something like this

SELECT *
FROM myTable
WHERE datediff("n",DTime, (SELECT Max(DTime) FROM myTable as T WHERE empID=myTable.empID AND DTime<myTable.DTime))>30 AND Date(Cdate)>Date()-30

 

[thmsjlmnt3953]

Hi,

I've tried to iput my tables and fields into that query to get it to work however im struggling,
The first part i think ive got ok is;

SELECT public_salesdetail.happened, [fname] & " " & [lname] AS employee, public_employee.id
FROM public_employee INNER JOIN public_salesdetail ON public_employee.id = public_salesdetail.checker_id

However im not sure how to set out the datediff part of the query in regards to my data

 

[CJ_London]

There is a small typo in my code but in your query builder remove what I said before and put this - changing the DTime to the name of your date/time field and myTable to the name of the table where DTime resides and the same for empID

datediff("n",DTime, (SELECT Max(DTime) FROM myTable as T WHERE empID=myTable.empID AND DTime<myTable.DTime))

in a new column field row

and in the criteria put

>30

in another column put in the field row

Date(DTime) *** this was my typo

and in the criteria put

>Date()-30

 

[thmsjlmnt3953]

Hi,

Date(DTime) throws up an error with the wrong amount of criteria when i try to put it into a new column as field