Quote:
Originally posted by vb_dba
OK, lets try this for starters -
declare 3 variables (MinCheckIn, MinCheckOut, MaxCheckOut) as date/time datatype.
Set MinCheckIn = Earliest CheckIn for the employee
Set MinCheckOut = Checkout for same transaction As MinCheckIn
Set MaxCheckOut = Max(CheckOut) Where CheckIn Between MinCheckIn And MinCheckOut And CheckOut > MinCheckOut
If MaxCheckOut Is Null (no records found) Set MaxCheckOut = MinCheckOut
Now MinCheckIn and MaxCheckOut should hold the overlapping time values.
So If we had:
CheckIn CheckOut
----------------------------------
8:00 16:00
12:00 14:00
13:00 17:00
MinCheckIn would be 8:00 and MaxCheckOut would be 17:00.
I've got to run right now. I'll look at it more in the morning. Play with the idea above and if it confuses you, let me know and I'll work with you in the morning.
Where do we go from here? I thought I had it working but I was wrong. Thanks, Jeremy