Comparing Records using SQL? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Comparing Records using SQL?

Author

Topic

ChrisBulmer
Starting Member

1 Post

Posted - 2009-03-30 : 13:46:38

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

wakkowarner
Starting Member

4 Posts

Posted - 2009-04-01 : 12:38:44

Add a HolidayID field as an Autonumber to tblHoliday to guarantee a unique identifier.

Then make your query:


SELECT *
FROM tblHoliday AS A, tblHoliday AS B
WHERE A.HolidayID <> B.HolidayID 
AND ((A.StartDate Between B.StartDate And B.EndDate) 
OR (A.EndDate Between B.StartDate And B.EndDate));

NOTE:This can make "duplicate" entries. That is, if the employee's holidays overlap for startdate and enddate, then an entry will appear for both overlaps. If you want to make it more unique, then this should at least get you started.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources