March 19, 2014 at 12:11 am
Hi,
I have three tables EmpIDs,EmpRoles and LatestRoles. I need to write a select Query to get roles of all employees present in EmpIDs table by referring EmpRoles and LatestRoles.
Where I stuck : The condition is first look into table EmpRoles and if it has more than one entry for a particular Employee ID than only need to get the Role from LatestRoles other wise consider the role from EmpRoles .
Example:
Create Table #EmpIDs
(
EmplID int
)
Create Table #EmpRoles
(
EMPID int,
Designation varchar(50)
)
Create Table #LatestRoles
(
EmpID int,
Designations varchar(50)
)
Insert into #EmpIDs values (1),(2),(3)
Insert into #EmpRoles values (1,'Role1'),(2,'Role1'),(2,'Role2'),(3,'Role1')
Insert into #LatestRoles values (2,'Role2')
Employee ID 2 is having two roles defined in EmpRoles so for EmpID 2 need to fetch Role from LatestRoles table and for remaining ID's need to fetch from EmpRoles .
My Final Output of select query should be like below.
EmpID Role
1 Role1
2 Role2
3 Role1
Please help.
March 19, 2014 at 12:36 am
Can't help thinking this would be a whole lot easier if you had a date field in the EmployeeRoles table...
Then you could just get the EmployeeID and Role With the MAX Date, and that would be the latest one, and it would be super easy.
use TempDB;
GO
CREATE TABLE Employee(
EmployeeID INT IDENTITY(100,1)
, FirstName VARCHAR(20)
, LastName VARCHAR(20)
CONSTRAINT pkEmployee PRIMARY KEY (EmployeeID));
GO
INSERT INTO Employee(FirstName,LastName) VALUES ('Homer','Simpson'),('Marge','Simpson'),('Bart','Simpson'),('Lisa','Simpson');
CREATE TABLE EmployeeRole(
EmployeeID INT
,RoleName VARCHAR(25)
,StartDate DATE
CONSTRAINT pkEmployeeRole PRIMARY KEY (EmployeeID,RoleName),
CONSTRAINT fkEmployeeID FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID));
INSERT INTO EmployeeRole(EmployeeID,RoleName,StartDate) VALUES (100,'Husband','3-1-1985'),
(100,'Father','6-6-1986'),
(101,'Wife','3-1-1985'),
(101,'Mother','6-6-1986');
-- return the latest role for each employee...
SELECT er.EmployeeID, RoleName, StartDate
FROM EmployeeRole er
INNER JOIN
(SELECT EmployeeID
,MAX(StartDate) AS LastDate
FROM EmployeeRole
GROUP BY EmployeeID) lr
ON (er.EmployeeID = lr.EmployeeID AND er.StartDate = lr.LastDate);
March 19, 2014 at 12:36 am
WITH CTE (EmplID,previousRole,currentRole) AS
(
SELECT #EmpIDs.EmplID,
#EmpRoles.Designation AS [Previous Role],
#LatestRoles.Designations AS [Current Role]
from #EmpIDs
INNER JOIN #EmpRoles ON #EmpIDs.EmplID = #EmpRoles.EmpID
LEFT OUTER JOIN #LatestRoles ON #EmpIDs.EmplID = #LatestRoles.EmpID
)
SELECT EmplID AS ID ,previousRole AS [Role]
FROM CTE
WHERE currentRole IS NULL
UNION ALL
SELECT DISTINCT EmplID AS ID ,currentRole AS [Role]
FROM CTE
WHERE currentRole IS NOT NULL
ORDER BY ID
___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.
March 19, 2014 at 12:36 am
SELECT EI.EmplID,ER.Designation FROM #EmpIDs AS EI
INNER JOIN #EmpRoles AS ER
ON EI.EmplID = ER.EMPID
WHERE EI.EmplID IN (1,3)
UNION ALL
SELECT * FROM #LatestRoles
ORDER BY EmplID
March 19, 2014 at 12:40 am
kumariannapureddy (3/19/2014)
SELECT EI.EmplID,ER.Designation FROM #EmpIDs AS EIINNER JOIN #EmpRoles AS ER
ON EI.EmplID = ER.EMPID
WHERE EI.EmplID IN (1,3)
UNION ALL
SELECT * FROM #LatestRoles
ORDER BY EmplID
You shouldnt write queries that work only with specific values 😉
___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.
March 20, 2014 at 2:41 am
This works with the data provided.
Selectdistinct
a.EmplID,
Case when c.EmpID is null then b.Designation else c.Designations End as [Role]
from #EmpIDs a
inner join #EmpRoles b on a.EmplID = b.EmpID
left outer join #LatestRoles c on a.EmplID = c.EmpID
If you have any control over the way the application is written, my preference would be for the current role to be written to #LatestRoles always regardless of whether there are previous roles or not. Then you just have a simple 2 table inner join to perform between #EmpIDs and #LatestRoles.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply