Dear Amit:

First a small detail. If a student has a certain EndDate and another

student has that as a StartDate, is this an overlap?

Now, to do this, I think you need to join the Students table on itself.

This means you consider the Students table twice in the same query.

It may be like this:

SELECT S1.RoomRefer, S1.StudentRef, S2.StudentRef

FROM Students S1

INNER JOIN Students S2

ON S1.RoomRefer = S2.RoomRefer

At this point, the query will show all students that have ever occupied the

same room in all combinations of pairs. This will ignore (for the moment)

whether the stays overlapped.

Now, to filter out those that do not overlap.

To find overlaps, add this to the above (at the end):

WHERE (S1.StartDate >= S2.StartDate

AND S1.StartDate <= S2.EndDate)

OR (S1.EndDate >= S2.StartDate

AND S1.EndDate <= S2.EndDate)

Finally, you need to eliminate the overlap of a student with himself:

AND S1.StudentRef <> S2.StudentRef

Then add your ORDER BY if you wish.

If you want to ignore when the end date of one student is the start date of

another, change the second part:

WHERE (S1.StartDate >= S2.StartDate

AND S1.StartDate < S2.EndDate)

OR (S1.EndDate > S2.StartDate

AND S1.EndDate <= S2.EndDate)

Now, this says Joe and Mark shared the room, but also Mark and Joe shared

it. To eliminate this duplication, change the last line I gave you:

AND S1.StudentRef < S2.StudentRef

Finally, add S1.StartDate, S1.EndDate, S2.StartDate, and S2.EndDate to the

SELECT clause if you want to see them. You will probably want to add

aliases.

Please let me know if this helped, and if I can be of any other assistance.

Tom Ellison

DowningDevelopments said:

I have a table called students which shows information about students on a

database for a student housing company. I am trying to figure out which

students have been allocated to the same room at the same time.

Every student has a [students].[RoomRef] which says where they are staying

and ive done a find duplicates query of the RoomRef.

However this also shows previous tenants in the same room which is fine,

what im after is students who are in teh room at the same time

(misallocated). To do this i want to use the [students].[startDate] and

[students].[endDate] to compare if the end date of one student overlaps

with

the startdate of the next, if there is then there has been a

misallocation.

So far my duplicates query looks like this:

SELECT Students.RoomRefer, Students.StudentRef, Students.StartDate,

Students.EndDate

FROM Students

WHERE (((Students.RoomRefer) In (SELECT [RoomRefer] FROM [Students] As Tmp

GROUP BY [RoomRefer] HAVING Count(*)>1 )))

ORDER BY Students.RoomRefer;

How could i change this to show if there are overlaps for tenants of the

same room?

With much thanks for any help

Amit