Comparing records from the same table

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

SQL Server 2000 Forums

SQL Server Development (2000)

Comparing records from the same table

Author

Topic

shrenikv
Starting Member

1 Post

Posted - 2003-01-15 : 12:40:52

Here is my problem:

I have an Employee table and I want to compare each record with previous record using SQL only. Is there a way to do this ? example

I want all the employees who were transfer to department XYZ from department ABC during the year 2000. It is possible that employee 001 moved from XYZ to PQR and then PQR to ABC in the year 2000 - In this case I want both the records XYZ to PQR and PQR to ABC.

Thanks
Shrenik

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-01-15 : 12:44:27

quote:

... I want to compare each record with previous record using SQL only ...

Remember you're dealing with sets, not a collection of "records" that have record #'s in an ISAM-style flat file such as Dbase. In SQL Server the only data you have to work with is the column values for every row in the table. There is no notion of absolute record order.

Post the DDL for the table and we'll take a closer look.

Jonathan
{0}

Edited by - setbasedisthetruepath on 01/15/2003 12:44:51

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2003-01-15 : 12:48:23

CREATE TABLE Departments (DepartmentID INT, Name VARCHAR(128))
CREATE TABLE Employees (EmployeeID INT, Name VARCHAR(128))
CREATE TABLE DepartmentEmployeeHistory (EmployeeID INT, FromDepartmentID INT, ToDepartmentID INT, Date DATETIME)

DELCARE @FromDepartmentID INT, @ToDepartmentID INT, @Year SMALLINT

SELECT @FromDepartmentID = DepartmentID FROM Departments WHERE Name = 'ABC'
SELECT @ToDepartmentID = DepartmentID FROM Departments WHERE Name = 'XYZ'
SET @Year = 2000

SELECT e.Name AS Employee, fd.Name AS FromDepartment, td.Name AS ToDepartment
FROM DepartmentEmployeeHistory AS deh
INNER JOIN Departments AS fd ON deh.FromDepartmentID = fd.DepartmentID
INNER JOIN Departments AS td ON deh.ToDepartmentID = td.DepartmentID
INNER JOIN Employees AS e ON deh.EmployeeID = e.EmployeeID
WHERE e.EmployeeID IN (SELECT EmployeeID FROM DepartmentEmployeeHistory WHERE FromDepartmentID = @FromDepartmentID AND ToDepartmentID = @ToDepartmentID AND YEAR(Date) = @Year)
)

/*
I think this will work... of course you'll need to adapt it to your schema since you didn't provide one
*/

Edited by - onamuji on 01/15/2003 12:49:16

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-01-15 : 13:01:26

Also look at:

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=21280

The idea of GROUPING by fields that should be the same and then checking the COUNT(*) is what I consider the easiest way to solve this type of problem. it also handles Nulls just fine which JOINS have trouble with.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources