开发者

sql show current instance

here is the assignment that I had to face:

List the employees who have transferred between departments during their employment. You should show their current department, and the date when the transferred to the current department. This is a pretty tough query. My solution was to use a subquery to determine which employees have been in more than one department, and use that result in the base query.

The problem that I don't know how to display the employee who have been transfered and only once. The way to tell if the employee has been transfered is if in the EmployeeDepartmentHistory table, the employee id has been in more than one record (i.e. employeeID 1 is in both record 1 and record 2 because the person has been in two departments). Ho开发者_开发问答w would I go about this? Here's what I have as of now:

SELECT EmployeeDepartmentHistory.EmployeeID,Person.Contact.FirstName, Person.Contact.LastName, Department.Name
From   HumanResources.Department INNER JOIN   
       HumanResources.EmployeeDepartmentHistory ON 
       HumanResources.Department.DepartmentID =
       HumanResources.EmployeeDepartmentHistory.DepartmentID INNER JOIN
       HumanResources.Employee ON HumanResources.EmployeeDepartmentHistory.EmployeeID
       = HumanResources.Employee.EmployeeID INNER JOIN
       Person.Contact ON HumanResources.Employee.ContactID = Person.Contact.ContactID
WHERE EmployeeDepartmentHistory.EmployeeID=(SELECT COUNT(HumanResources.EmployeeDepartmentHistory.EmployeeID)
        FROM HumanResources.EmployeeDepartmentHistory
        WHERE EmployeeDepartmentHistory.EmployeeID = Employee.EmployeeID
        Group by EmployeeDepartmentHistory.EmployeeID)


Not sure if I get the requirement correct. Would you want to try HAVING clause at the end?

That is:

HAVING COUNT(EmployeeDepartmentHistory.EmployeeID) = 2

(2 - assuming that the EmployeeDepartmentHistory will contain the current department as well)

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜