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


    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.


    --> 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

    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

  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