How to get the Manager by Manager in SQL

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

General SQL Server Forums

New to SQL Server Programming

How to get the Manager by Manager in SQL

Author

Topic

senthilavs
Starting Member

5 Posts

Posted - 2012-03-07 : 03:06:50

Hi,

I want to find the approval level person id by passing current login person id. Form the below records "Kumaran", "Ramalingam" and "Senthil" are the approval person.
Approval Level: "Karthik" --> "Senthil" --> "Ramalingam" --> "Kumaran"

Eg:

1. "Karthik" and "Sanmugam" are users. Both of them reporting to "Senthil".
2. "Senthil" is reporting to "Ramalingam". Also once "Senthil" is approved the "Ramalingam" can view the "Senthil" approvals.
3. "Ramalingam" is reporting to "Kumaran". Also once "Ramalingam" is approved the "Kumaran" can view the "Ramalingam" approvals.

My Query Requirement.

1. If "Kumaran" login to the System he need "Ramalingam" id to check the leave approved by "Ramalingam".
2. If "Ramalingam" login to the System he need "Senthil" id to check the leave approved by "Senthil".

DECLARE @_StaffID INT
SET @_StaffID = 1 --Kumaran
SET @_StaffID = 2 --Ramalingam
SET @_StaffID = 3 --Senthil
CREATE TABLE #ReportingToPerson
(
Staff_ID INT,
Staff_Name VARCHAR(50),
Reporting_To_ID INT
)

INSERT INTO #ReportingToPerson VALUES (1, 'Kumaran', NULL)
INSERT INTO #ReportingToPerson VALUES (2, 'Ramalingam', 1)
INSERT INTO #ReportingToPerson VALUES (3, 'Senthil', 2)
INSERT INTO #ReportingToPerson VALUES (4, 'Karthik', 3)
INSERT INTO #ReportingToPerson VALUES (5, 'Sanmugam', 3)
INSERT INTO #ReportingToPerson VALUES (6, 'Ashok', 2)

SELECT ReportTo.Staff_ID, ReportTo.Staff_Name, ReportTo.Reporting_To_ID
FROM #ReportingToPerson LoggedID
INNER JOIN #ReportingToPerson AS ReportTo ON LoggedID.Staff_ID = ReportTo.Reporting_To_ID
WHERE LoggedID.Staff_ID = @_StaffID

DROP TABLE #ReportingToPerson

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-07 : 19:05:37

see below

http://msdn.microsoft.com/en-us/library/ms186243.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources