2nd highest working details of employee

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

2nd highest working details of employee

Author

Topic

Mahavirjadhav
Starting Member

18 Posts

Posted - 2012-11-19 : 01:23:29

Dear All,

I am having tables
1. EmployeeInfo(emp_id, empname, DOB,DOJ)
2. EmployeeWorkHistory(id,empId, deptid, fromdate,todate)

EmployeeInfo:
emp_id empname DOB DOJ
1001 abc 1/1/1985 1/1/2005
1002 xyz 12/12/1986 1/2/2006

EmployeeWorkHistory:

id empid deptid fromdate todate
1 1001 1 1/1/2005 31/12/2005
2 1001 2 1/1/2006 31/12/2006
3 1001 3 1/1/2007 31/12/2007
4 1002 2 1/2/2006 31/12/2006
5 1002 3 1/1/2007 31/12/2007
6 1002 4 1/1/2008 31/12/2008
7 1002 1 1/1/2009 31/12/2009
8 1001 4 1/1/2007 31/12/2007
9 1001 2 1/1/2008 31/12/2011
10 1002 3 1/1/2010 31/12/2011

I want following result
empname deptid fromdate todate
abc 4 1/1/2007 31/12/2007
xyz 1 1/1/2009 31/12/2009

Kindly help me.
Thanks in advance.

Mahavir

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2012-11-19 : 01:26:20

It seems to be your homework.

Study about joins in BOL..

Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-19 : 02:22:08

looks like this


SELECT ei.empname,ewh.deptid,ewh.fromdate,ewh.todate
FROM EmployeeInfo ei
INNER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY fromdate DESC,todate DESC) AS Seq,*
            FROM EmployeeWorkHistory
           )ewh
ON ewh.empid = ei.Emp_id
AND ewh.Seq=2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Mahavirjadhav
Starting Member

18 Posts

Posted - 2012-11-19 : 02:42:55

Thanks dude. It works. Thanks again.

quote:
Originally posted by visakh16

looks like this

SELECT ei.empname,ewh.deptid,ewh.fromdate,ewh.todate
FROM EmployeeInfo ei
INNER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY fromdate DESC,todate DESC) AS Seq,*
FROM EmployeeWorkHistory
)ewh
ON ewh.empid = ei.Emp_id
AND ewh.Seq=2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Mahavir

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-19 : 02:46:54

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources