SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 2nd highest working details of employee
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Mahavirjadhav
Starting Member

India
15 Posts

Posted - 11/19/2012 :  01:23:29  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1007 Posts

Posted - 11/19/2012 :  01:26:20  Show Profile  Visit senthil_nagore's Homepage  Send senthil_nagore a Yahoo! Message  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/19/2012 :  02:22:08  Show Profile  Reply with Quote
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/

Go to Top of Page

Mahavirjadhav
Starting Member

India
15 Posts

Posted - 11/19/2012 :  02:42:55  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/19/2012 :  02:46:54  Show Profile  Reply with Quote
welcome

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000