Find previous results based on current values

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

Transact-SQL (2000)

Find previous results based on current values

Author

Topic

majorbroncosfan
Starting Member

2 Posts

Posted - 2004-05-11 : 11:13:05

I am not new to SQL, but I seem to have hit a brain freeze.

I am trying to run a query to find previous results based on current values. For example, I would like to find all employees who have worked with us for several years who are working in a department that is different from where they have been in the past. We have the following tables (pertinent information shown):

EMPLOYEE
EmployeeID
FirstName
LastName
DepartmentID

DEPARTMENT
DepartmentID
Name

I have tried using correlated subqueries and other methods and am trying to avoid using cursors for the sake of performance. If there is anybody who can help me with this issue, I would be grateful.

Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-11 : 12:07:59

Did you save the record historically?

Are they datetime stamped?

Brett

8-)

majorbroncosfan
Starting Member

2 Posts

Posted - 2004-05-11 : 12:14:48

Yes, they have the date and time for each change.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-05-11 : 12:21:44

Assuming there is a startdatetime and enddatetime on the employee table (This should have been included with your table layout in the first post) then something like this might work:

select a.EmployeFirstName, a.EmployeeLastName, b.Name, a.StartdateTime, a.EnddateTime
FROM Employee a
JOIN Department b
on a.DepartmentID = b.DepartmentID
WHERE a.StartDateTime <> (SELECT MAX(StartDateTime) FROM Employee where EmployeeID = a.EmployeeID)

Duane.

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-11 : 12:22:08

Well if you post the DDL, and some sample data

Like

INSERT INTO yourTable(Collist)
SELECT 'sample data', other cvols, ect UNION ALL
SELECT 'sample data', other cvols, ect UNION ALL
SELECT 'sample data', other cvols, ect...

I'm sure we can come up with the answer...

To tell you though, it's gonna be something like:

DateCol < MAX(DateCol)

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-05-11 : 15:24:15

what is the key of your Employee table? Where is the date and time for each change stored? there's no implication of any history in the two tables you've shown us.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources