SQL Server Performance

How to get the Manager by Manager in SQL

Discussion in 'ALL SQL SERVER QUESTIONS' started by senthilavs, Mar 6, 2012.

  1. senthilavs New Member

    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.
  2. Shehap MVP, MCTS, MCITP SQL Server

    Could you share that table of leaves since I could see the above query work fine to get the hierarchal employment

Share This Page