Query

[Deam]

I am trying to create a view in which I want to see the prior department for an employee which may or may
not be in the 1st history record.
This view will find the last time the department code was changed and the date it was changed.
There are 3 tables:
TableEmpData = EmployeeID, fullname etc
TableDepartmentData = DepartmentID,DepartmentCode
TableLocationData = EmployeeID,DepartmentID,LocationStartDate

This is the sql I have so far(which is not working):
SELECT DISTINCT TOP 100 PERCENT dbo.TableEmpData.EmployeeID, dbo.TableEmpData.FullName, MAX(l.LocationStartDate)
AS LastDepartmentChangeDate,
d.DepartmentCode AS LastDepartmentChanged
FROM dbo.TableLocationData l INNER JOIN
dbo.TableDepartmentData d ON l.DepartmentCode = d.DepartmentCode INNER JOIN
dbo.TableEmpData ON l.EmployeeID = dbo.TableEmpData.EmployeeID
WHERE (d.DepartmentCode <>
(SELECT TableDepartmentData.DepartmentCode
FROM TableDepartmentData INNER JOIN
TableLocationData ON TableDepartmentData.DepartmentCode
= TableLocationData.DepartmentCode
WHERE TableLocationData.EmployeeID = l.EmployeeID AND TableLocationData.LocationEndDate
= DATEADD(minute,- 1,l.LocationStartDate)))
GROUP BY dbo.TableEmpData.FullName, d.DepartmentCode, dbo.TableEmpData.EmployeeID

 

[grahamm]

What is the KEY for TableLocationData?

 

[Deam]

The key for TableLocationData and TableEmpData is EmployeeID
and for TableDepartmentData is DepartmentID