Next Record analysis?

[antiskeptic]

Here's my situation...
I have "time" records in SQL that do not have an order or sequence number. So I have simply ordered them by the DetailDate and then by the StartTime.
They are records of employee time, that keep track of when the employee punched in / Punched out...when they took lunch, etc. Unfortuneately...unless the employee specifically punches out for LUNCH, it does not record a specific "break". So if I were to add the "TotalHours" for the same date below...I would come up with 8:00 hours even.

DetailDate PayPeriod TimeIn TimeOut TotalHours
2004-06-12 2004-06-25 7:59A 12:19P 04:20
2004-06-12 2004-06-25 1:23P 5:03P 03:40


Under certain laws a person working that long is entitled to a one hour break. If they do not take it on thier own, the company pays them for a 1 hour break an a manager manually enters that one hour. If, however, they took the break the company would not pay them for that extra time.

In the record above...you can see that the employee did indeed take an hour break. But how do I analyze the TimeOut punch to the NEXT TimeIn punch?

Any Ideas?

Sincerely,
Antiskeptic

 

[MDXer]

is there an employeeID field or something that ties the record to an employee?

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[antiskeptic]

yes,

so it would look like this:

Emp# DetailDate PayPeriod TimeIn TimeOut TotalHours
0124 2004-06-12 2004-06-25 7:59A 12:19P 04:20
0124 2004-06-12 2004-06-25 1:23P 5:03P 03:40

 

[twifosp]

I guess it depends on what you want to analze... the time difference to see if they are taking more or less than one hour?

If so you could do

select emp#,
datediff(mm,(select max(timeout) from table where emp# = a.emp# and detaildate = a.detaildate),(select min(timein) from table where emp# = a.emp# and detaildate = a.detaildate) )
from table a

This assumes that an employee would only take one break a day.

 

[antiskeptic]

Thank you for responding...

Sorry if I didn't make that part clear...yes, I am wanting to analyze the time difference between the StopTime and the Next StartTime record. It is usual that they only take one break a day. (but of course as with any rule there will always be exceptions) But right now I am just looking to get the ones who "play by the rules", so to speak. I tried the DateDiff in the example above, but I get and Arithmetic Overflow error when I attempt it.

Know how to get around this? Or another option?

SIncerely,
AntiSkeptic

 

[twifosp]

Hrm, you shouldn't be getting one of those if you data types are datetime between both columns? Are they? If not, try casting them as such.

Are you casting the whole expression as a decimal? That would be a more likely cause of such an error.

Can you paste the entire query?

Also -- if you need to work around the employee possibly taking more than one break, you should really consider adding a unique entry id as a primary key to the table.