Overlapping Time Spans Query
-
Hi all,
I’ve had a pondering over this for quite a few hours now with no luck. Maybe someone could be so kind as to lend a hand?
This query result is looking at a table and returning any date/time ranges that overlap each other.
As you can see I’m using a self-referencing query (table linking to itself). I then return the fields twice, firstly for the left table then again for the right table.
SELECT DISTINCTt1.id,
t1.empid,
t1.jobnum,
t1.opcode,
t1.starttime,
t1.finishtime,
t2.id id2,
t2.empid empid2,
t2.jobnum jobnum2,
t2.opcode opcode2,
t2.starttime starttime2,
t2.finishtime finishtime2
FROM
vGSL_DMTEpicorData t1 inner join
vGSL_DMTEpicorData t2
on t1.jobnum=t2.jobnum and
t1.empid=t2.empid and
(
t1.starttime between dateadd(s,1,t2.starttime) and dateadd(s,-1,t2.finishtime) OR
t1.finishtime between dateadd(s,1,t2.starttime) and dateadd(s,-1,t2.finishtime)
)
WHERE
t1.empid <>'0' and
t1.starttime >'2014-10-01 00:00:00.000'
order by
empid
The query seems to work to some extend but I am having a problem with duplicates appearing. Well, not quite duplicates….
All the rows are perfectly fine and have identified an overlapping time, however, the ones highlighted are the duplicates. As you can see, they are duplicate times, but being compared in the opposite direction (left to right, and right to left). Yellow Is the same as the other yellow, and blue is the same as the other blue.
How would you go about preventing this kind of duplicate and only showing 1 of them? My sql knowledge isn’t great so maybe I’m going about this the wrong way.
Many thanks for any help!!
MF
-
Provided Id is unique just add
AND t1.ID < t2.ID
to ON clause.
-
serg-52 (10/30/2014)
Provided Id is unique just addAND t1.ID < t2.ID
to ON clause.
Thanks for the hasty reply!
ID is indeed Unique but ID and ID2 could be the same because they are both coming from the same table.
Trying your suggestion unfortunately does not give the desired results as its remove the valid lines as well (shown as strikethroughs in the image)
-
Yep, overlapping condition ... Generaly, overlapping means
t1.starttime < t2.finishtime AND t2.starttime < t1.finishtime
i.e those intervals have at least one common point. And this condition is symmetric.
Sorry, haven't noticed you are using different condition which is not symmetric.
If the above meaning is correct for your purpose, try symmetric one.
-
serg-52 (10/30/2014)
Yep, overlapping condition ... Generaly, overlapping meanst1.starttime < t2.finishtime AND t2.starttime < t1.finishtime
i.e those intervals have at least one common point. And this condition is symmetric.
Sorry, haven't noticed you are using different condition which is not symmetric.
If the above meaning is correct for your purpose, try symmetric one.
Thanks again for the reply.
I as essentially looking for any times in table1 (t1) that intersect any times in the same table (t2) where the jobnum and empid match.
The times are from a time booking system - an employee books his time to jobs throughout the day and this is to check to see if he/she has accidentally put times in that overlap each other (i.e. no employee can work on more than 1 job at a time).
I'm not quite sure what you mean as symmetric conditions or how to try them?
-
Symmetric condition , i mean for two intervals, just dates for example, (2014-10-30, 2104-11-02) and (2014-10-29, 2014-11-03) no matter which is t1 and which is t2 symmetric intersection condition gives true. So it will always return two rows for intersecting intervals. And t1.ID <t2.Id leaves only one of them in the result set.
Your original condition depends on which interval is t1 and which is t2 so it's not symmetric. In some cases it returns two rows and in some cases only one as for above example.
-
So try
SELECT DISTINCTt1.id,
t1.empid,
t1.jobnum,
t1.opcode,
t1.starttime,
t1.finishtime,
t2.id id2,
t2.empid empid2,
t2.jobnum jobnum2,
t2.opcode opcode2,
t2.starttime starttime2,
t2.finishtime finishtime2
FROM
vGSL_DMTEpicorData t1 inner join
vGSL_DMTEpicorData t2
on t1.jobnum=t2.jobnum and
t1.empid=t2.empid and
(
t1.starttime < t2.finishtime and
t2.starttime < t2.finishtime and
t1.id < t2.id
)
WHERE
t1.empid <>'0' and
t1.starttime >'2014-10-01 00:00:00.000'
order by
empid
-
serg-52 (10/30/2014)
So trySELECT DISTINCTt1.id,
t1.empid,
t1.jobnum,
t1.opcode,
t1.starttime,
t1.finishtime,
t2.id id2,
t2.empid empid2,
t2.jobnum jobnum2,
t2.opcode opcode2,
t2.starttime starttime2,
t2.finishtime finishtime2
FROM
vGSL_DMTEpicorData t1 inner join
vGSL_DMTEpicorData t2
on t1.jobnum=t2.jobnum and
t1.empid=t2.empid and
(
t1.starttime < t2.finishtime and
t2.starttime < t2.finishtime and
t1.id < t2.id
)
WHERE
t1.empid <>'0' and
t1.starttime >'2014-10-01 00:00:00.000'
order by
empid
Thank you so much, this does indeed seem to catch all the correct rows! Had to tweak your example slightly, guess it was just a typo on your behalf.
Changed from
(t1.starttime < t2.finishtime and
t2.starttime < t2.finishtime and
t1.id < t2.id
)
to
(t1.starttime < t2.finishtime and
t2.starttime < t1.finishtime and
t1.id < t2.id
)
You were very helpful serg.
If i can give you rep/points after this reply I will! 🙂
-
Yes, it must be as you've corrected.
You are welcome.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply