Comparing Records using SQL?

[ChrisB]

Hi,

Im trying to compare records from one table with others from the same table. My table (tblHoliday) has the EmployeeID, StartDate and EndDate. Im trying to find any records where the "holiday" overlaps (where one record is entirely or partly within the range of dates of another record). I've figured that i might need to use 2 tables so ive created tblTempHoliday and created some code to copy all of tblHoliday into tblTempHoliday.

How would you do the comparison, im pretty stuck?

Thanks,

Chris

 

[ezfriend]

Since two tables are identical, you can probably
- use a selected statement to get all the records from tblTwo
- loop through your recordset and compare to tblOne to see if the StartDate of the current record falls within a date range in tblOne or EndDate falls within a date range int tblOne where the record id in tblTwo <> record id in tblOne. This will avoid comparing the record to itself since Record #1 in tblOne & Record #1 in tblTwo are the same.

Hope this help a little bit.

EZ