Oracle Query for getting CURRENT CTC (Salary) of Each Employee

Question

i want current CTC of each employee following is the design of my table

Ecode      Implemented Date            Salary
7654323    2010-05-20                  350000
7654322    2010-05-17                  250000
7654321    2003-04-01                  350000
7654321    2004-04-01                  450000
7654321    2005-04-01                  750000
7654321    2007-04-01                  650000

i want oracle query for following output

Ecode          Salary
7654321        650000   
7654322        250000 
7654323        350000

thanks in advance

See also

Oracle Query for getting MAximum CTC (Salary) of Each Employee

Answer 1

can you please elaborate your question...?

Answer 2

SELECT * 
FROM salary s
INNER JOIN 
  (SELECT ecode, MAX(implemented_date) as implemented_date 
   FROM salary GROUP BY ecode) curr
ON curr.ecode = s.ecode AND curr.implemented_date = s.implemented_date

Answer 3

Hi,

If you want to keep the last salary for each ecode sorted by implemented_date:

SQL> WITH data AS (
  2     SELECT 7654323 Ecode, '2010-05-20' Implemented_Date, 350000 Salary
  3       FROM DUAL
  4     UNION ALL SELECT 7654322, '2010-05-17', 250000 FROM DUAL
  5     UNION ALL SELECT 7654321, '2003-04-01', 350000 FROM DUAL
  6     UNION ALL SELECT 7654321, '2004-04-01', 450000 FROM DUAL
  7     UNION ALL SELECT 7654321, '2005-04-01', 750000 FROM DUAL
  8     UNION ALL SELECT 7654321, '2007-04-01', 650000 FROM DUAL
  9  )
 10  SELECT ecode,
 11         MAX(salary)
 12             KEEP (dense_rank FIRST ORDER BY Implemented_Date DESC) sal
 13    FROM DATA
 14   GROUP BY ecode;

     ECODE        SAL
---------- ----------
   7654321     650000
   7654322     250000
   7654323     350000

Answer 4

I'd use analytical functions for that. You want to select the first value of salary for each combination of ecode and implementeddate ordered by the implementeddate to put the latest at the top.

select
    distinct
    first_value(ecode) OVER (PARTITION BY ecode ORDER BY IMPLEMENTEDDATE DESC NULLS LAST) Ecode, 
    first_value(implementeddate) OVER (PARTITION BY ecode ORDER BY IMPLEMENTEDDATE DESC NULLS LAST) ImplementedDate,
    first_value(salary) OVER (PARTITION BY ecode ORDER BY IMPLEMENTEDDATE DESC NULLS LAST) Salary
from 
    tbl_Salary;

The "DISTINCT" will keep null rows at bay that would otherwise be returned for the other 3 versions of Ecode=7654321 that we're filtering out.

The result is:

ECODE  IMPLEMENTEDDATE  SALARY
-----  ---------------  ------
7654321     01/04/2007      650000
7654322     17/05/2010      250000
7654323     20/05/2010      350000