Need help with employee hierrarchy

Question

Need help with employee hierrarchy

sunitkrishna

SSCrazy

Points: 2233
More actions
July 2, 2014 at 5:25 am

#308691

Hi,
I need help with the following query:
----------------------------------------------------------------------------------------------
declare @LoginId nvarchar(20)
set @LoginId='A-1519'
declare @LoginName nvarchar(100)
set @LoginName=(select [FirstName] + ISNULL(''+[MiddleName],'') + ISNULL(''+[LastName],'')
from [dbo].[Employee] where EmpId=@LoginId)
;WITH EmployeeTree(EmpId,FullName,DesignationName,ReportingMgr,LoginMgr)--,TreeLevel)
AS (
SELECT EmpId,[FirstName] + ISNULL(''+[MiddleName],'') + ISNULL(''+[LastName],''),
(SELECT D.[DesignationName] FROM [dbo].[Designation] D WHERE D.Status=0 AND D.DesignationID=Designation) AS DesignationName,
ReportingManager,@LoginName--,'1'
FROM [dbo].Employee
where EmpId=@LoginId and [Status]=0 AND ISNULL(IsEmployee,0)=0
union all
SELECT EmpId,[FirstName] + ISNULL(''+[MiddleName],'') + ISNULL(''+[LastName],''),
(SELECT D.[DesignationName] FROM [dbo].[Designation] D WHERE D.Status=0 AND D.DesignationID=Designation) AS DesignationName,
ReportingManager,@LoginName--,'2'
FROM [dbo].Employee
where ltrim(rtrim(ReportingManager))=@LoginName
and [Status]=0 AND ISNULL(IsEmployee,0)=0
UNION ALL
SELECT e.EmpID,[FirstName] + ISNULL(''+[MiddleName],'') + ISNULL(''+[LastName],''),
(SELECT D.[DesignationName] FROM [dbo].[Designation] D WHERE D.Status=0 AND D.DesignationID=Designation) AS DesignationName,
ReportingManager,@LoginName--,'3'
FROM [dbo].Employee e
JOIN EmployeeTree p ON p.FullName= e.ReportingManager
)
SELECT *
FROM EmployeeTree
----------------------------------------------------------------------------------------------
Emp|IdNameDesignation Reporting Manager LoginMgr
A-0001XPM Jain Jain
A-5665YSSE Jain Jain
A-9090WSE X Jain
A-6666SSE X Jain
This is how the result appears now.
I need like this:
EmpIdNameDesignationReporting Manager LoginMgr
A-0001XPM Jain Jain
A-9090WSE X Jain
A-6666SSE X Jain
A-5665YSSE Jain Jain
I hope my question is understood.
regards,
Sunitha
---
Thinking is the hardest work there is, which is the probable reason so few engage in it.
Sunitha
😎

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Is the order your only problem?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

sunitkrishna (7/2/2014)
Hi,
I need help with the following query:
----------------------------------------------------------------------------------------------
declare @LoginId nvarchar(20)
set @LoginId='A-1519'
declare @LoginName nvarchar(100)
set @LoginName=(select [FirstName] + ISNULL(''+[MiddleName],'') + ISNULL(''+[LastName],'')
from [dbo].[Employee] where EmpId=@LoginId)
;WITH EmployeeTree(EmpId,FullName,DesignationName,ReportingMgr,LoginMgr)--,TreeLevel)
AS (
SELECT EmpId,[FirstName] + ISNULL(''+[MiddleName],'') + ISNULL(''+[LastName],''),
(SELECT D.[DesignationName] FROM [dbo].[Designation] D WHERE D.Status=0 AND D.DesignationID=Designation) AS DesignationName,
ReportingManager,@LoginName--,'1'
FROM [dbo].Employee
where EmpId=@LoginId and [Status]=0 AND ISNULL(IsEmployee,0)=0
union all
SELECT EmpId,[FirstName] + ISNULL(''+[MiddleName],'') + ISNULL(''+[LastName],''),
(SELECT D.[DesignationName] FROM [dbo].[Designation] D WHERE D.Status=0 AND D.DesignationID=Designation) AS DesignationName,
ReportingManager,@LoginName--,'2'
FROM [dbo].Employee
where ltrim(rtrim(ReportingManager))=@LoginName
and [Status]=0 AND ISNULL(IsEmployee,0)=0
UNION ALL
SELECT e.EmpID,[FirstName] + ISNULL(''+[MiddleName],'') + ISNULL(''+[LastName],''),
(SELECT D.[DesignationName] FROM [dbo].[Designation] D WHERE D.Status=0 AND D.DesignationID=Designation) AS DesignationName,
ReportingManager,@LoginName--,'3'
FROM [dbo].Employee e
JOIN EmployeeTree p ON p.FullName= e.ReportingManager
)
SELECT *
FROM EmployeeTree
----------------------------------------------------------------------------------------------
Emp|IdNameDesignation Reporting Manager LoginMgr
A-0001XPM Jain Jain
A-5665YSSE Jain Jain
A-9090WSE X Jain
A-6666SSE X Jain
This is how the result appears now.
I need like this:
EmpIdNameDesignationReporting Manager LoginMgr
A-0001XPM Jain Jain
A-9090WSE X Jain
A-6666SSE X Jain
A-5665YSSE Jain Jain
I hope my question is understood.
regards,
Sunitha

Some readily consumable test data would be helpful in getting an answer to your question. Please see the first link under "Helpful Links" in may signature line below for the correct way to do such a thing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sunitkrishna SSCrazy Points: 2233 More actions · Answer 3

Hi,

Sorry for that Jeff.You can find the sample data here.

-------------------------------------------------------

declare @Tmp Table(EmpId nvarchar(10),FullName nvarchar(60),DesignationName nvarchar(100),ReportingMgr nvarchar(60),LoginMgr nvarchar(60))

insert into @Tmp values('A-4981','Rupen','VP-SD','Moris','Rupen')

insert into @Tmp values('A-4982','Joe','SSDM','Rupen','Rupen')

insert into @Tmp values('A-4983','Roy','SSDM','Rupen','Rupen')

insert into @Tmp values('A-4984','Litty','SSDM','Rupen','Rupen')

insert into @Tmp values('A-4985','Lopez','SDM','Joe','Rupen')

insert into @Tmp values('A-4986','Vinnie','SDM','Joe','Rupen')

insert into @Tmp values('A-4987','Samad','ASDM','Lopez','Rupen')

insert into @Tmp values('A-4988','Jeffin','ASDM','Vinnie','Rupen')

select * from @Tmp

------------------------------------------------------------------

This is the possible result of the query i originally posted.

I would like to know if i coudld get them ordered in a hierrarchical way as in the following :

-------------------------------------------------------------------------------