If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Overlapping time
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! -- clueless |
#2
|
|||
|
|||
Overlapping time
Try changing and to = and =
"clueless" wrote: 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! -- clueless |
Thread Tools | |
Display Modes | |
|
|