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.
| Author |
Topic |
|
EugeneLim11
Posting Yak Master
167 Posts |
 Posted - 2008-03-24 : 07:56:31
|
| I have a table employeeDepartmentHistory with the following fields1. EmployeeID, GUID (FK to Employee Table)2. DepartmentID,GUID (FK to departmentTable)3. StartDate, DateTime (Record the date which the employee start working in the department)4. EndDate, DateTime (Record the date which employee left the department)My question is how do I check for duplicate records in StartDate for each Employee? e.g. EmployeeID Department StartDate EndDateJohn Sales 13 Mar 2008 13 Mar 2008John Accounts 13 Mar 2008 nullEmber Sales 14 Mar 2008 nullApple Sales 15 Mar 2008 nullI want to list all records for an employee who has identical start date (e.g. John) but I could not come out with the correct SQL statement to get what I need. Any help is very much appreciated. It should display EmployeeID Department StartDate EndDateJohn Sales 13 Mar 2008 13 Mar 2008John Accounts 13 Mar 2008 null |
|
|
pravin14u
Posting Yak Master
246 Posts |
Posted - 2008-03-24 : 08:06:03
|
| select distinct e1.EmpId,e1.DeptId,E1.StartDate from emp_detail e1inner join emp_detail e2on e1.empid=e2.empidand e1.deptid<>e2.deptidand e1.startdate=e2.startdate |
 |
|
|
EugeneLim11
Posting Yak Master
167 Posts |
Posted - 2008-03-24 : 08:18:39
|
| Thank you. Funny how I could not get it, but seems so simple to others. |
|
|
|
pravin14u
Posting Yak Master
246 Posts |
Posted - 2008-03-24 : 08:36:46
|
| Please note that the above query will not return any rows if the duplication is for the same department |
|
|
|
|
|
|
Query Question