time tracking

[stag246]

I am having trouble figuring out what to do with this here…

We have a web-based form where people enter times for employees that take
care of consumers. Most employees have more then one consumer so they have to
fill out more then one time sheet to be entered into the web-based form but
there are some employees that are putting a time say like below that they
would traveling from 2:15 til 3:00 and be at a Consumer’s place from 2:45 til
3:15 which means that they would be in two places at once. Would there be a
way I can set up a report or something that would verify that they were not
double charging the company?


time checking
id date | consumerNO |fromTime | toTime | earnCode
1 3/1/2004 12 2:00 pm 2:15 pm Train
1 3/1/2004 12 2:15pm 3:00 pm Trvl
1 3/1/2004 14 2:45 pm 3:15pm Con
1 3/1/2004 14 3:30 pm 3:45 pm Train
1 3/1/2004 667 4:00 pm 4:30pm Trvl


Thanks,

Jeremy

 

[Michel Walsh]

Hi,



SELECT a.id, a.date, a.FromTime, a.ToTime,b.FromTime, b.ToTime
FROM myTable As a INNER JOIN myTable As b
ON a.id=b.id and a.date=b.date and a.FromTime < b.ToTime AND a.ToTime

b.FromTime

Basically, there is no overlap if, and only if

a.FromTime> b.ToTime ("a" started after the end of "b")
OR
a.ToTime < b.FromTime ( "a" ended before the start of "b')


There is some overlap, partial or complete, in the negation, so, using De
Morgan laws, we got the previous formulation.


It is generally preferable to keep the date with the time, to avoid problems
around midnight. The actual formulation does not handle these cases.

In theory, if a overlap b, then b overlap a, so each overlap will be listed
twice. To avoid a double listing (of the same problem), you can add a clause
like:


AND a.primaryKey < b.primaryKey



Hoping it may help,
Vanderghast, Access MVP

 

[stag246]

Hello mike.
sorry I didnt reply earlier. It was supposed to notify me when there was a
response to my question but that didnt happen. anyways I changed the table so
that the time would be in a short time format aka military time. I have the
following fields: 'empid' (text format), 'date' (date and time format),
'fromtime' & 'totime' (both are date/time format), and there is a 'id' (which
is an long interger field which is just autonumbered when the time is
entered)

here is the query i modified to use but it just gives me more records then
were originally in my table

SELECT a.id, a.date, a.FromTime, a.ToTime
FROM timecheck AS a INNER JOIN timecheck AS b ON (a.empid=b.empid) AND
(a.date=b.date) AND (a.FromTime<b.ToTime) AND (a.ToTime>b.FromTime);


Is there something I am doing wrong? It can return the id of the record, the
time from and to and the date but if need be can be cut down the the id of
the record. Thanks for the help so far.

Jeremy

 

[Michel Walsh]

HI,


Is it possible that a given interval overlap more than one other
interval?


You can use a SELECT DISTINCT to remove duplicated selected rows.



Hoping it may help,
Vanderghast, Access MVP

 

[stag246]

I tried:
SELECT DISTINCT a.id, a.date, a.FromTime, a.ToTime, a.empid
FROM timecheck AS a INNER JOIN timecheck AS b ON (a.ToTime>b.FromTime) AND
(a.FromTime<b.ToTime) AND (a.date=b.date) AND (a.empid=b.empid)
ORDER BY a.Date;

but it just returned this (just a small example of it)

id empid date FromTime ToTime
151720 AdamsR 3/1/2004 15:00 17:30
151725 AdamsR 3/1/2004 9:00 13:00
151721 AdamsR 3/2/2004 15:00 17:30
151726 AdamsR 3/2/2004 9:00 13:00
151722 AdamsR 3/3/2004 15:00 17:30
151727 AdamsR 3/3/2004 9:00 13:00
151728 AdamsR 3/4/2004 9:00 13:00
151723 AdamsR 3/4/2004 15:00 17:30
151724 AdamsR 3/5/2004 15:00 17:30
151729 AdamsR 3/5/2004 9:00 13:00
158939 AdamsR 3/8/2004 9:00 13:00
158944 AdamsR 3/8/2004 15:00 17:30
158945 AdamsR 3/9/2004 15:00 17:30
158940 AdamsR 3/9/2004 9:00 13:00
158941 AdamsR 3/10/2004 9:00 13:00
158946 AdamsR 3/10/2004 15:00 17:30
158942 AdamsR 3/11/2004 9:00 13:00
158947 AdamsR 3/11/2004 15:00 17:30
158943 AdamsR 3/12/2004 9:00 13:00
158948 AdamsR 3/12/2004 15:00 17:30
id empid date FromTime ToTime
166067 Bittin 3/23/2004 8:00 10:45
166068 Bittin 3/23/2004 12:00 14:45
166069 Bittin 3/23/2004 16:00 18:30
166070 Bittin 3/24/2004 8:00 10:45
166071 Bittin 3/24/2004 12:00 14:45
166072 Bittin 3/24/2004 16:00 18:30
166075 Bittin 3/25/2004 16:00 18:30
166074 Bittin 3/25/2004 12:00 14:45
166073 Bittin 3/25/2004 8:00 10:45

 

[Michel Walsh]

Hi,


With the following data:


timeCheck
empID date FromTime ToTime pk
0 2004-03-01 14:00:00 14:15:00 1
0 2004-03-01 14:15:00 15:00:00 2
0 2004-03-01 14:45:00 15:15:00 3
0 2004-03-01 15:30:00 15:45:00 4
0 2004-03-01 16:00:00 16:30:00 5



the query

SELECT DISTINCT a.empid, a.date, a.FromTime, a.ToTime, b.FromTime, b.ToTime
FROM timeCheck AS a INNER JOIN timeCheck As b
ON a.empID=b.EmpID AND a.date=b.date AND a.FromTime < b.ToTime AND a.ToTime

b.FromTime AND a.pk < b.pk

returns

Query46
empid date a.FromTime a.ToTime b.FromTime b.ToTime
0 2004-03-01 14:15:00 15:00:00 14:45:00 15:15:00




which is the only overlap.



Have you a small set of data than can reproduce your problem?


Hoping it may help,
Vanderghast, Access MVP

 

[stag246]

The query works great thanks so much. I had a table of about 220,000 records
and it found 512 errors which is about .23% error ratio not to bad for the
first 11 months of the year. Thanks again. But one quick question where you
have the a.pk < b.pk would this only look at the records after the a.pk what
happens if there is a match that is above a.pk. that wouldnt show it correct
or am i missing something?

thanks again...major props need to be given for query.

it helped tons

 

[Michel Walsh]

Hi,


Yep, you are definitively right... BUT that is NOT a problem if you
consider the following: if record "a" overlaps (partially or in full)
record "b", then, also, record "b" overlaps record "a". And also, "a"
overlaps "a" and "b" overlaps "b". Of these four "mathematically right"
result, we are only interested in only "one" meaningful: either one of the
first two. Stating a.pk < b.pk just does the trick: it removes the 3
"unwanted" results.



Hoping it may help,
Vanderghast, Access.

 

[stag246]

Thanks so much for all the help. It works great and found alot of time
overlapping. Thanks again.

Jeremy

 

[Mary]

Michel and MVPs,
I have the same problem and have tried using your SQL below with no luck. I
also posted in response to another string, but haven't got a response, so I'm
trying this one which looks to be the same scenario.

I have a table with a StartDate and an EndDate. I need a query that will
prompt for a date range, and list any record that is ACTIVE during that
timeframe, so there will be overlapping.

My fields are:

StartDate
EndDate
CR_ID
Status

The table is linked with Oracle. No date format is shown in Table Design
and I cannot edit because of the link. The display is m/d/yy hh:nn, unless
the time is midnight, then it's just m/d/yy.

Can you help?

Thanks! Mary