CODEWITHSUNDEEP
sqlsql-serversql-server-2008
ALDHEEB
ALDHEEB

Reputation: 111

Select last salary

I have Two Table ( My example):

TB1

Emp_code     salary     year   month 
-------      -----      ----   -----
1             2000       2015   1
1             2000       2015   2 
2             4000       2015   2
2             4000       2015   3

TB2

code          name       Age 
-------      -----      ----   
1             MMMM       32
1             MMMM       32 
2             KKKK       25   
2             KKKK       25

I need select the last (month and year) get salary >>

the Result should be show like the following table>>

code          name       Age    salary   year   month
-------      -----      ----    ------   ----   -----
1             MMMM       32      2000    2015     2
2             KKKK       25      4000    2015     3

Upvotes: 1

Views: 2236

Answers (4)

mangesh
mangesh

Reputation: 365

create script for employee and salary table

CREATE TABLE [dbo].[Employee](
[Empcode] [varchar](50) NULL,
[Name] [varchar](50) NULL,
[Age] [varchar](50) NULL) ON [PRIMARY]


CREATE TABLE [dbo].[Salary](
[Empcode] [varchar](50) NULL,
[Salary] [varchar](50) NULL,
[Year] [varchar](50) NULL,
[Month] [varchar](50) NULL
) ON [PRIMARY]

salary table data

1   10000   2017    1
1   10000   2017    2
2   40000   2017    2
2   40000   2017    3

employee table data

1   aaa 25
2   bbb 25

Query :-

select e.empcode,e.name,e.age,s.salary,max(s.year) as year,max(s.month) as month from salary s join employee e on s.empcode=e.empcode group by e.empcode,e.name,e.age,s.salary

output :-

1   aaa 25  10000   2017    2
2   bbb 25  40000   2017    3

Upvotes: 0

Mansoor
Mansoor

Reputation: 4192

Try below statements to get your desired result ;      

CREATE TABLE #Table(Emp_code INT,salary INT,_year INT,  _month INT)
CREATE TABLE #Table1(code INT,name VARCHAR(100), Age INT)

INSERT INTO  #Table(Emp_code ,salary , _year ,   _month)
SELECT 1,2000,2015,1 UNION ALL
SELECT 1,2000,2015,2 UNION ALL 
SELECT 2,4000,2015,2 UNION ALL
SELECT 2,4000,2015,3

INSERT INTO  #Table1(code ,name , Age)
SELECT 1,'MMMM',32 UNION ALL
SELECT 1,'MMMM',32 UNION ALL 
SELECT 2,'KKKK',25 UNION ALL
SELECT 2,'KKKK',25 

SELECT DISTINCT code ,name , Age , salary , _year , A._month
FROM #Table1
JOIN
(
  SELECT Emp_code ,MAX(_month) _month
  FROM #Table
  GROUP BY Emp_code 
) A ON Emp_code = code  
JOIN #Table T ON A.Emp_code = T.Emp_code AND A._month = T._month

Upvotes: 0

Jibin Balachandran
Jibin Balachandran

Reputation: 3441

SELECT *
FROM ( 
       SELECT t1.code,
              t2.name,
              t2.Age,
              t1.salary,
              t1.year,
              t1.month,
              ROW_NUMBER OVER(PARTITION BY t1.Emp_code ORDER BY t1.year desc,t1.month desc) AS RN 
       FROM TB1 t1
       INNER JOIN TB2 t2 ON t1.Emp_code=t2.code
                                                 ) X
WHERE X.RN=1

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Use window functions and distinct:

select . . .
from (select tb1.*,
             row_number() over (partition by emp_code order by year desc, month desc) as seqnum
      from tb1
     ) tb1 join
     (select distinct tb2.*
      from tb2
     ) tb2
     on tb1.code = tb2.emp_code;

Upvotes: 0

Related Questions