How to get max startdate in each employee of one table

Posted by Ramakrishna188 under Sql Server on 2/9/2013 | Points: 10 | Views : 2062 | Status : [Member] | Replies : 2

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hello all,

I have employeement history table in this table empid, jobtitle, startdate, enddate columns are there.
each employee can enter more than one time his jobtile each time he select startdate and enddate
but my requirement is i want to get max startdate jobtile in each employee. suppose i have 20 employees so
i want to get 20 jobtitles. Please tell me any one.

Thanks

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Bachanrawat on: 2/9/2013 [Member] Starter | Points: 25

0	select jobtile , min(startdate ) from tableName group by jobtile order by jobtile it will work fine. Ramakrishna188, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Pandians on: 2/9/2013 [Member] [MVP] Silver | Points: 25

0	Check it out! Sample table and Data CREATE TABLE Employeement ( EmployeementID INT IDENTITY(1,1), EmpID INT, JOBTitle VARCHAR(100), StartDate DATETIME, EndDate DATETIME) GO INSERT Employeement(EmpID,JOBTitle,StartDate,EndDate) VALUES(1001,'SQL Developer','2000-01-01','2004-04-10') INSERT Employeement(EmpID,JOBTitle,StartDate,EndDate) VALUES(1001,'SQL Junion DBA','2004-04-11','2006-05-11') INSERT Employeement(EmpID,JOBTitle,StartDate,EndDate) VALUES(1001,'SQL DBA','2006-05-12','2011-04-10') INSERT Employeement(EmpID,JOBTitle,StartDate,EndDate) VALUES(1001,'SQL Senior DBA','2011-04-11','2012-09-15') GO INSERT Employeement(EmpID,JOBTitle,StartDate,EndDate) VALUES(1002,'Developer','2000-11-11','2003-04-24') INSERT Employeement(EmpID,JOBTitle,StartDate,EndDate) VALUES(1002,'Application Developer','2003-04-25','2006-05-11') INSERT Employeement(EmpID,JOBTitle,StartDate,EndDate) VALUES(1002,'Senior Application Developer','2006-05-12','2011-08-11') GO To get MAX or Latest JOBtitle info., of each employee ;WITH LatestTitle AS ( SELECT ROW_NUMBER() OVER(PARTITION BY EmpID ORDER BY StartDate DESC) [Req],EmployeementID FROM Employeement ) SELECT E.* FROM Employeement E JOIN LatestTitle LT ON (E.EmployeementID = LT.EmployeementID) WHERE LT.Req =1 GO Cheers www.SQLServerbuddy.blogspot.com iLink Multitech Solutions Ramakrishna188, if this helps please login to Mark As Answer. \| Alert Moderator

Check it out!

Sample table and Data

CREATE TABLE Employeement

(

EmployeementID	INT IDENTITY(1,1),

EmpID		INT,

JOBTitle	VARCHAR(100),

StartDate	DATETIME,

EndDate		DATETIME)

GO



INSERT Employeement(EmpID,JOBTitle,StartDate,EndDate) VALUES(1001,'SQL Developer','2000-01-01','2004-04-10')

INSERT Employeement(EmpID,JOBTitle,StartDate,EndDate) VALUES(1001,'SQL Junion DBA','2004-04-11','2006-05-11')

INSERT Employeement(EmpID,JOBTitle,StartDate,EndDate) VALUES(1001,'SQL DBA','2006-05-12','2011-04-10')

INSERT Employeement(EmpID,JOBTitle,StartDate,EndDate) VALUES(1001,'SQL Senior DBA','2011-04-11','2012-09-15')

GO



INSERT Employeement(EmpID,JOBTitle,StartDate,EndDate) VALUES(1002,'Developer','2000-11-11','2003-04-24')

INSERT Employeement(EmpID,JOBTitle,StartDate,EndDate) VALUES(1002,'Application Developer','2003-04-25','2006-05-11')

INSERT Employeement(EmpID,JOBTitle,StartDate,EndDate) VALUES(1002,'Senior Application Developer','2006-05-12','2011-08-11')

GO

To get MAX or Latest JOBtitle info., of each employee

;WITH LatestTitle

AS

(

SELECT ROW_NUMBER() OVER(PARTITION BY EmpID ORDER BY StartDate DESC) [Req],EmployeementID FROM Employeement

)



SELECT E.* FROM Employeement E JOIN LatestTitle LT ON (E.EmployeementID = LT.EmployeementID) 

WHERE LT.Req =1

GO

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Ramakrishna188, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts