Need guideance

[Guest]

I need to do the following and am at a loss of where to even look in my
books. Any ideas where to start of what to read about would be appreciated.
I'll try to explain as simple as I can.

I have the following table: field1, field2, date, timein, timeout

Need to look at all records with dates that match. Then identify if field1
is duplicated within that set. If field1 is duplicated for the date I need
to identify any overlap in time.

 

[Guest]

Not to be facetious, but did you look up the DateDiff function in VBA help?

Randall

 

[Guest]

I'm not sure how you want to handle the matching dates-- do you plan to group
recordsets based on date and then run your comparisons within each group?

Anyway, the time difference part is easy. Use the VBA DateDiff function to
get differences between any two dates (including times). Also note that date
values in SQL strings must lie between opening and closing # characters.

Randall Arnold

 

[Guest]

I guess I'm suffering the same caffeine shortage...lol. I saw that you
wanted the delta (overlap) between times on the same date and just figured
the DateDiff function was the key. If not, I apologize for wasting your time.

Randall

 

[Guest]

This will take some medium to advanced level VBA. How strong are you in VBA?

 

[Guest]

Okay I did the duplicate query which groups my data by date and field1. But
sorry, I don't understand how to use DateDiff to find out if times overlap
for example on one given day:

field1 starttime stoptime
==== ====== ======
Smith 8:58 10:58
Smith 7:37 10:24

I need to say huh, in two places as once..... and throw up a msg box.

 

[Guest]

guess I'm a medium....i just need a place to start.....

 

[Tim Ferguson]

(I have renamed the illegal field name date to recorddate)

Okay I did the duplicate query which groups my data by date and
field1. But sorry, I don't understand how to use DateDiff to find out
if times overlap for example on one given day:

field1 starttime stoptime
==== ====== ======
Smith 8:58 10:58
Smith 7:37 10:24

I need to say huh, in two places as once..... and throw up a msg box.

select field1, recorddate, starttime, stoptime
from mytable as l
inner join mytable as r
on l.field1 = r.field1
and l.recorddate = r.recorddate
and l.stoptime < r.starttime
and l.starttime < r.stoptime


I have not tested this, but it should be close. The idea is to create a
self join on all matching field1 and recorddate values, then pick
combinations that overlap.

I would be very interested to know if it works...

All the best


Tim F

 

[Tim Ferguson]

I have not tested this, but it should be close. The idea is to create a
self join on all matching field1 and recorddate values, then pick
combinations that overlap.

I've tested this and it seems to be okay: note that the field names are
not necessarily the same:

SELECT l.fname, l.recorddate,
l.starttime as start1, r.starttime as start2,
l.stoptime as stop1, r.stoptime as stop2

FROM timeclocks AS l INNER JOIN timeclocks AS r
ON (l.recorddate = r.recorddate) AND (l.fname = r.fname)

where l.starttime < r.starttime
and r.starttime < l.stoptime

I would be very interested to know if it works...

No, it wouldn't.

Best wishes


Tim F

 

[Guest]

Thanks Tim. Think I'm going to create an array and store the records in
order by date, person, start time. Then check to be sure start time is
greater than one prior if it is then check to see if the start time is less
than the stop on the same record. If it is there's overlap. I'm having a
though time getting my hands around this one.

 

[Tim Ferguson]

Thanks Tim. Think I'm going to create an array and store the records
in order by date, person, start time. Then check to be sure start
time is greater than one prior if it is then check to see if the start
time is less than the stop on the same record. If it is there's
overlap. I'm having a though time getting my hands around this one.

I don't really understand. The query I suggested [1] extracts all pairs
of records where the start time of the second record falls in between the
start and end time of the first one. What else do you want?

[1] It's likely to fail when the start times of the records are the same:
e.g. 12:00 to 14:00 and 12:00 to 17:00. You'll need to change the
comparison operator from

where l.starttime < r.starttime

to

where l.starttime <= r.starttime


Perhaps I misunderstood the question, though.


Tim F

 

[Marshall Barton]

Thanks Tim. Think I'm going to create an array and store the records
in order by date, person, start time. Then check to be sure start
time is greater than one prior if it is then check to see if the start
time is less than the stop on the same record. If it is there's
overlap. I'm having a though time getting my hands around this one.

I don't really understand. The query I suggested [1] extracts all pairs
of records where the start time of the second record falls in between the
start and end time of the first one. What else do you want?

[1] It's likely to fail when the start times of the records are the same:
e.g. 12:00 to 14:00 and 12:00 to 17:00. You'll need to change the
comparison operator from

where l.starttime < r.starttime

to

where l.starttime <= r.starttime

Tim, you had a typo in your query. It should be:
. . .
WHERE l.starttime < r.stoptime <<<<<<
And r.starttime < l.stoptime

 

[Tim Ferguson]

Tim, you had a typo in your query. It should be:
. . .
WHERE l.starttime < r.stoptime <<<<<<
And r.starttime < l.stoptime

It wasn't a typo: it was a direct cut-and-paste from the SQL view of the
query designer!

Don't think it's a mistake either. The criterion is meant to select
records where the second one starts after the first one's start but
before its stop.

Having said that, the logic is flawed because it doesn't work when the
start times are the same or when start and end times are both equal.

Here is another evolution:

SELECT l.FName, l.RecordDate,
l.StartTime AS start1,
l.StopTime AS stop1,
r.StartTime AS start2,
r.StopTime AS stop2
FROM timeclocks AS l INNER JOIN timeclocks AS r
ON l.FName = r.FName AND l.RecordDate = r.RecordDate
WHERE l.RecordID < r.RecordID
AND l.StartTime <= r.StartTime
AND r.StartTime <= l.Stoptime


This time it uses the RecordID field to prevent (a) matching a record
with itself and (b) double counting of the same pair twice. One problem
remains: when there are three (or more) overlapping records, the query
will produce one row for each pair; but to be honest I cannot think of a
logical way of presenting anything different.

Best wishes


Tim F

 

[Marshall Barton]

Marshall Bartonwrote

It wasn't a typo: it was a direct cut-and-paste from the SQL view of the
query designer!

Don't think it's a mistake either. The criterion is meant to select
records where the second one starts after the first one's start but
before its stop.

Having said that, the logic is flawed because it doesn't work when the
start times are the same or when start and end times are both equal.

Here is another evolution:

SELECT l.FName, l.RecordDate,
l.StartTime AS start1,
l.StopTime AS stop1,
r.StartTime AS start2,
r.StopTime AS stop2
FROM timeclocks AS l INNER JOIN timeclocks AS r
ON l.FName = r.FName AND l.RecordDate = r.RecordDate
WHERE l.RecordID < r.RecordID
AND l.StartTime <= r.StartTime
AND r.StartTime <= l.Stoptime


This time it uses the RecordID field to prevent (a) matching a record
with itself and (b) double counting of the same pair twice. One problem
remains: when there are three (or more) overlapping records, the query
will produce one row for each pair; but to be honest I cannot think of a
logical way of presenting anything different.

Tim, I see what you're saying, but I think ot should be
more like:

SELECT L.FName,
L.StartTime AS Start1, L.Stoptime AS Stop1,
R.StartTime AS Start2, R.Stoptime AS Stop2
FROM timeclocks AS L INNER JOIN timeclocks AS R
ON L.FName = R.FName
AND L.RecordDate = R.RecordDate
AND L.StartTime < R.StartTime
WHERE L.StartTime < R.Stoptime
AND R.StartTime < L.Stoptime
ORDER BY L.FName, L.StartTime

 

[Tim Ferguson]

FROM timeclocks AS L INNER JOIN timeclocks AS R
ON L.FName = R.FName
AND L.RecordDate = R.RecordDate
AND L.StartTime < R.StartTime

WHERE L.StartTime < R.Stoptime
AND R.StartTime < L.Stoptime

ORDER BY L.FName, L.StartTime

On testing this, it doesn't get all the test cases; in particular when
two start times are identical (eg 12:00-14:00 and 12:00-13:00). This one
gets all the test cases that I can come up with:

SELECT L.FName, L.RecordDate,
L.StartTime AS start1, L.StopTime AS stop1,
R.StartTime AS start2, R.StopTime AS stop2

FROM timeclocks AS L INNER JOIN timeclocks AS R
ON L.FName = R.FName
AND L.RecordDate = R.RecordDate
AND L.StartTime <= R.StartTime
AND L.RecordID <> R.RecordID

WHERE R.StartTime < L.Stoptime

ORDER BY L.FName, L.StartTime

I don't think this misses any overlaps.

The only problem is that pairs with identical start times are listed
twice; and that when there are three or more overlaps each overlapping
pair is listed. It's probably possible to remove these with a bunch of
IIF clauses.

Alternatively, using a SELECT DISTINCT to reduce the output to provide
just FName and RecordDate also squashes the duplication (and is logically
more consistent).

Is the OP still with us?


Best wishes


Tim F

 

[Marshall Barton]

Marshall Barton wrote

[]

FROM timeclocks AS L INNER JOIN timeclocks AS R
ON L.FName = R.FName
AND L.RecordDate = R.RecordDate
AND L.StartTime < R.StartTime

WHERE L.StartTime < R.Stoptime
AND R.StartTime < L.Stoptime

ORDER BY L.FName, L.StartTime

On testing this, it doesn't get all the test cases; in particular when
two start times are identical (eg 12:00-14:00 and 12:00-13:00). This one
gets all the test cases that I can come up with:

SELECT L.FName, L.RecordDate,
L.StartTime AS start1, L.StopTime AS stop1,
R.StartTime AS start2, R.StopTime AS stop2

FROM timeclocks AS L INNER JOIN timeclocks AS R
ON L.FName = R.FName
AND L.RecordDate = R.RecordDate
AND L.StartTime <= R.StartTime
AND L.RecordID <> R.RecordID

WHERE R.StartTime < L.Stoptime

ORDER BY L.FName, L.StartTime

I don't think this misses any overlaps.

The only problem is that pairs with identical start times are listed
twice; and that when there are three or more overlaps each overlapping
pair is listed. It's probably possible to remove these with a bunch of
IIF clauses.

Alternatively, using a SELECT DISTINCT to reduce the output to provide
just FName and RecordDate also squashes the duplication (and is logically
more consistent).

Is the OP still with us?

Tim, I'm getting a headache looking for counter examples
here. Maybe your query works, but there is a lack of
symmetry that bothers me.

Copping out by bowing to someone smarter than me (see
http://allenbrowne.com/appevent.html), I think this may be a
correct paraphrase (ignoring the Null issues) of Allen's
query using a Join instead of a cartesian product (which are
probably the same thing by the time it gets to the db
engine).

SELECT L.FName, L.RecordDate,
L.StartTime AS start1, L.StopTime AS stop1,
R.StartTime AS start2, R.StopTime AS stop2

FROM timeclocks AS L INNER JOIN timeclocks AS R
ON L.FName = R.FName
AND L.RecordDate = R.RecordDate
AND L.RecordID <> R.RecordID

WHERE R.StartTime < L.Stoptime
AND L.StartTime < R.Stoptime

ORDER BY L.FName, L.RecordDate, L.StartTime

I really do not want to get into the issue of results being
returned twice since that strikes me as being a somewhat
existential question. After all, who's to say that interval
L conflicts with interval R instead of interval R
conflicting with interval L :-\

 

[Tim Ferguson]

Tim, I'm getting a headache looking for counter examples
here. Maybe your query works, but there is a lack of
symmetry that bothers me.

The idea was to break the symmetry, in order to avoid the double-
counting.

Copping out by bowing to someone smarter than me (see
http://allenbrowne.com/appevent.html), I think this may be a
correct paraphrase (ignoring the Null issues) of Allen's
query using a Join instead of a cartesian product (which are
probably the same thing by the time it gets to the db
engine).

Trust Allen to have done all this before! I was quite proud of the
solution there for a while. Working up his solution in does give the same
solution as your query and correctly identifies the overlaps.

I think that about wraps it up..?

All the best


Tim F

 

[Marshall Barton]

The idea was to break the symmetry, in order to avoid the double-
counting.


Trust Allen to have done all this before! I was quite proud of the
solution there for a while. Working up his solution in does give the same
solution as your query and correctly identifies the overlaps.

I think that about wraps it up..?

I'm wrapped up, but we still have not heard what Sash thinks
of all this ;-)