How to get max startdate in each employee of one table

Posted by Ramakrishna188 under Sql Server on 2/9/2013 | Points: 10 | Views : 1100 | Status : [Member] | Replies : 2
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




Responses

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

Up
0
Down
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

Up
0
Down
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

Login to post response