How to get the Manager by Manager in SQL

Question

Post reply

How to get the Manager by Manager in SQL

Senthil Varadharajan

Mr or Mrs. 500

Points: 545
More actions
March 6, 2012 at 11:44 pm

#254361

Hi,
I want to fetch the record based on the below condition
"Karthik" and "Sanmugam" are users. "Senthil" is head of the department. If "Senthil" approve the leave then "Ramalingam" can view and if "Ramalingam" is approved then "Kumaran" can view the details. In my query i want to know the previous approval person id based on the logged in user id.
Eg:
--> IF "Kumaran" ID Passes means, result should display only "Ramalingam"
--> IF "Ramalingam" ID Passes means, result should display only "Senthil"
--> IF "Senthil" ID Passes means, no record should display
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)
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.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

--> IF "Senthil" ID Passes means, no record should display

What about:

INSERT INTO #ReportingToPerson VALUES (4, 'Karthik', 3)

INSERT INTO #ReportingToPerson VALUES (5, 'Sanmugam', 3)

?

Why they shouldn't be returned?

What make them different to Senthil when you query for Ramalingam?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Senthil Varadharajan Mr or Mrs. 500 Points: 545 More actions · Answer 2

Hi Eugene Elutin,

I want to find the approval level person id by passing current login person id. Form the above 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".

Thanks.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

May be my question wasn't clear...

How the record for 'Senthil' is different to record for 'Karthik'?

Both have Id's, Name's and Report_To_Id's?

What identifies Karthik being the user but Senthil - manager? The name?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Senthil Varadharajan Mr or Mrs. 500 Points: 545 More actions · Answer 4

Hi Eugene Elutin,

I missed one more Column called Department_Head. If Department_Head means value will be "1" else "0". "Senthil", "Ramalingam" and "Kumaran" are different department level heads. "Karthik" is user to particular deparment where "Senthil" is department head.

So once "Karthik" applies leave it will be shown to "Senthil", Once "Senthil" approved then it will be shown to "Ramalingam" ...

Thanks.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

That is exactly what I was asking for!

Here we are:

I guess your setup should be changed to:

CREATE TABLE #ReportingToPerson

(

Staff_ID INT,

Staff_Name VARCHAR(50),

Reporting_To_ID INT,

Department_Head BIT

)

INSERT INTO #ReportingToPerson VALUES (1, 'Kumaran', NULL, 1)

INSERT INTO #ReportingToPerson VALUES (2, 'Ramalingam', 1, 1)

INSERT INTO #ReportingToPerson VALUES (3, 'Senthil', 2, 1)

INSERT INTO #ReportingToPerson VALUES (4, 'Karthik', 3, 0)

INSERT INTO #ReportingToPerson VALUES (5, 'Sanmugam', 3, 0)

and your query then should check for Department_Head flag:

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 ReportTo.Department_Head = 1

AND LoggedID.Staff_ID = @_StaffID

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Senthil Varadharajan Mr or Mrs. 500 Points: 545 More actions · Answer 6

Thanks Eugene Elutin.

Eugene Elutin (3/7/2012)
That is exactly what I was asking for!
Here we are:
I guess your setup should be changed to:
CREATE TABLE #ReportingToPerson
(
Staff_ID INT,
Staff_Name VARCHAR(50),
Reporting_To_ID INT,
Department_Head BIT
)
INSERT INTO #ReportingToPerson VALUES (1, 'Kumaran', NULL, 1)
INSERT INTO #ReportingToPerson VALUES (2, 'Ramalingam', 1, 1)
INSERT INTO #ReportingToPerson VALUES (3, 'Senthil', 2, 1)
INSERT INTO #ReportingToPerson VALUES (4, 'Karthik', 3, 0)
INSERT INTO #ReportingToPerson VALUES (5, 'Sanmugam', 3, 0)
and your query then should check for Department_Head flag:
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 ReportTo.Department_Head = 1
AND LoggedID.Staff_ID = @_StaffID