CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Sonam Mohite
Sonam Mohite

Reputation: 903

how to get second highest number for multiple employee passed as parameter in stored procedure

I have table like Emp_salary

Emp_id  Salary  name  Branch_ID  Is_Current_Branch  Branch_name
1       1000     A     89           0                pune
1       2000     A     25           0                Mumbai  
1       3000     A     12           1                Bangalore

here the Is_current_Branch indicated the branch where this employee working currently(Bangalore), if i want to get his branch where he was working before current branch(i.e. Mumbai) for all the employee whose ID passed through parameter in S.P.

@Employee='1,2,3' (3 employee parameter)

How to do... please help me out

Upvotes: 0

Views: 227

Answers (2)

Darren
Darren

Reputation: 70748

You need to add a Date field on the table (To ensure it is the correct entry) and then sort by it:

ALTER TABLE Emp_salary
ADD [LeftDate] DATETIME

Then you could select from Emp_Table:

SELECT TOP 1 * FROM 
Emp_salary
WHERE IS_CURRENT_BRANCH != 1
AND Emp_id = 1
ORDER BY LeftDate DESC

Upvotes: 1

Romil Kumar Jain
Romil Kumar Jain

Reputation: 20775

This query will give the second last branch of every employee if the current branch is not their first branch.

Op has not supplied any of the increasing field, so we are taking the records in the order of their increased salary.

;WITH secondbranch 
     AS (SELECT *, 
                Row_number() 
                  OVER ( 
                    partition BY emp_id 
                    ORDER BY salary DESC) AS branchnumber 
         FROM   emp_salary) 
SELECT * 
FROM   secondbranch 
WHERE  branchnumber = 2

Upvotes: 1

Related Questions