C
clueless
Hi All,
I posted a question the other day. I wanted to be able to flag overlapping
time (end time overlapping with start time of the next task). It did not
make a difference if it was different employees or different tasks; the only
thing I was interested in was if the end time of the first task overlapped
the start time of the 2nd task and so on.
John Spencer kindly replied with the following code:
" SELECT Employee, Task, StartTime, EndTime
, Exists (SELECT * FROM SomeTable As Temp
WHERE Temp.StartTime > SomeTable.StartTime
AND Temp.StartTime < SomeTable.EndTime)
FROM SomeTable
That should return TRUE for line 2. If you want true for both line 1 and 2
then the criteria becomes more complex."
It appeared to work perfectly and then I noticed if there were multiple
start times that were identical for some reason it did not flag all of them.
Employee Task Start time End time Overlap
1. L 2 02:44 02:58
2. X 3 03:03 03:51
3. B 7 03:03 03:51
4. X 1 15:46
15:50 5. C
8 16:05 16:32
6. A 1 16:05 16:38 Yes
7. Z 5 16:32 16:44
8. C 4 23:58 00:07
9. D 5 00:02 00:09
From the above example, line 6 end time overlaps with line 7 start time
which is correct with the "yes". Line 2, 5 and 8 should show a "yes" but
does not. I am stuck and not sure how to correct the problem. Any help
would be greatly appreciated. Thank you in advance!
I posted a question the other day. I wanted to be able to flag overlapping
time (end time overlapping with start time of the next task). It did not
make a difference if it was different employees or different tasks; the only
thing I was interested in was if the end time of the first task overlapped
the start time of the 2nd task and so on.
John Spencer kindly replied with the following code:
" SELECT Employee, Task, StartTime, EndTime
, Exists (SELECT * FROM SomeTable As Temp
WHERE Temp.StartTime > SomeTable.StartTime
AND Temp.StartTime < SomeTable.EndTime)
FROM SomeTable
That should return TRUE for line 2. If you want true for both line 1 and 2
then the criteria becomes more complex."
It appeared to work perfectly and then I noticed if there were multiple
start times that were identical for some reason it did not flag all of them.
Employee Task Start time End time Overlap
1. L 2 02:44 02:58
2. X 3 03:03 03:51
3. B 7 03:03 03:51
4. X 1 15:46
15:50 5. C
8 16:05 16:32
6. A 1 16:05 16:38 Yes
7. Z 5 16:32 16:44
8. C 4 23:58 00:07
9. D 5 00:02 00:09
From the above example, line 6 end time overlaps with line 7 start time
which is correct with the "yes". Line 2, 5 and 8 should show a "yes" but
does not. I am stuck and not sure how to correct the problem. Any help
would be greatly appreciated. Thank you in advance!