Re: how to match time ranges for multiple records in a query?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

"mike" <mike_newsgroups@xxxxxxxxx> wrote in message
news:1131483428.302442.246690@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> Sorry for the late reply, for some reason google didn't show this
> thread on my recent threads with some activity.
>
> I changed the Schedules table to make this easier by removing the day
> column, now it looks like this (real data):
>
>
> ID userID timeStart timeEnd
> 87 8 11/7/2005 8:00:00 AM 11/7/2005 11:30:00 PM
[snip]
>
> What I want to do is find ALL of the overlapping times, the time range
> of the overlaps, and which userids are part of each overlap.
[snip]

SELECT S1.userID AS userID,
IIF(S1.timeStart > S2.timeStart, S1.timeStart, S2.timeStart) AS
timeStart,
IIF(S1.timeEnd < S2.timeEnd, S1.timeEnd, S2.timeEnd) AS timeEnd
FROM Schedule AS S1
INNER JOIN Schedule AS S2
ON S1.userID <> S2.userID
AND S1.timeStart < S2.timeEnd
AND S1.timeEnd > S2.timeStart


.