Select query - -Need help

[Deam]

I have a select statement that selects some data from different tables. I need to select the most recent of the foll fields: PerformedDate,EmptblPerformanceStartDate,EmptblPerformanceEndDate as even though an emp may be active but his performance may have ended a month back.

My Select statement goes:

SELECT vEmptbl.EmptblGUID,
tEmptblStatusHist.EmptblStatusStartDate,
tEmptblJobHist.EmptblJobStartDate,
tEmptblLocationHist.EmptblLocationStartDate, vEmptbl.Address + CHAR(13) + CHAR(10) +
vEmptbl.City + ', ' + vEmptbl.StateProvinceCode + ' ' + vEmptbl.PostalCode Address,
'(' + vEmptbl.DayAreaCode + ') ' + vEmptbl.DayPhone + ' Ext: ' + vEmptbl.DayExtension DayPhone,
'(' + vEmptbl.HomeAreaCode + ') ' + vEmptbl.HomePhone HomePhone,
vEmptbl.SSN_SIN,
vEmptbl.EmployeeID,
vEmptbl.ClockNo,
vEmptbl.FullName,
tEmptblSupervisor.FullName SupervisorFullName,
tEmptblPerformanceHist.PerformedDate,
tEmptblPerformanceHist.EmptblPerformanceStartDate,
tEmptblPerformanceHist.EmptblPerformanceEndDate,
'(' + tEmptblPerformanceHist.PerformanceRatingCode + ') ' + tPerformanceRating.PerformanceRatingDescription PerformanceRating,
'(' + tEmptblPerformanceHist.PerformanceReviewTypeCode + ') ' + tPerformanceReviewType.PerformanceReviewTypeDescription PerformanceReviewType,
tEmptblPerformanceHist.ScheduledReviewDate,
tEmptblJobHist.BusinessCardTitle,
FROM vEmptbl
INNER JOIN (SELECT * FROM tEmptblStatusHist WHERE EmptblStatusCurrentFlag = 1) tEmptblStatusHist
INNER JOIN tStatus ON tEmptblStatusHist.StatusCode = tStatus.StatusCode
ON vEmptbl.EmptblGUID = tEmptblStatusHist.EmptblGUID
LEFT JOIN tEmptbl tEmptblSupervisor ON tEmptblLocationHist.SupervisorEmptblGUID = tEmptblSupervisor.EmptblGUID
ON vEmptbl.EmptblGUID = tEmptblLocationHist.EmptblGUID
LEFT JOIN (SELECT * FROM tEmptblPerformanceHist) tEmptblPerformanceHist
ON vEmptbl.EmptblGUID = tEmptblPerformanceHist.EmptblGUID
LEFT JOIN tPerformanceReviewType ON tEmptblPerformanceHist.PerformanceReviewTypeCode = tPerformanceReviewType.PerformanceReviewTypeCode
LEFT JOIN tPerformanceRating ON tEmptblPerformanceHist.PerformanceRatingCode = tPerformanceRating.PerformanceRatingCode
WHERE tStatus.ActiveFlag = 1

 

[Deam]

Also...I am looking for a way where I do not need to group by all the select fields. I have a lot more than this...I just took them out to make it look short