Pull the latest Employee and Manager Comments Based on Modified date I have a table Comments which is linked to the Plan table. Each time the user adds a comment on a specific plan, the Comments table is populated. This table stores the historical data. I need to pull out the latest record with the employee comments and the manager comments.

Comments Table schema is as shown below:

EmpFK | PlanFK | Comments | ModifiedDate
  60       7     empCmnt1   13-04-2016
  61       7     mgrCmnt1   14-04-2016
  60       7     empCmnt2   18-04-2016
  61       7     mgrCmnt2   19-04-2016
  60       8     empcmnt1   01-03-2016 

Plan Table schema is as follows:

PlanPK |  PlanName
  7         Name1
  8         Name2

Employee Table Schema:

EmpPK | MgrPK  | EmpName
  60      61       user1
  61               mgr1

Here 60 is the empid and 61 is the mgrid. EmployeePK and the ManagerId column in the 'Employee' table gives the employee and manager details. Each employee will have only one manager. No higher lever consideration is needed in this scenario
Expected Result:

empid  |  mgrid  | EmpCmnt  |  MgrCmnt  | ModifiedDate | planFK
  60         61     empCmnt2    mgrCmnt2   19-04-2016       7
  60         61     empcmnt1               01-03-2016       8
Thank you for the schemas. I did modify the employee table to include a manager bit flag which makes the query a bit easier. This doesn't exactly reproduce your desired results, but it is very close. If there is no manager comment on the last plan do you need to include the manager ID? Also, I wasn't sure on the date to include so the date is there for each comment.

--Load base tables
CREATE TABLE #Comments 
(EmpFK INT
, PlanFK INT
, Comments VARCHAR(10)
, ModifiedDate DATE)

CREATE TABLE #Plan
(PlanFK INT
, PlanName VARCHAR(10))

CREATE TABLE #Employee
(EmpPK INT
, MgrPk INT NULL
, Mgr BIT
, EmpName VARCHAR(10))

INSERT INTO #Comments VALUES (60,7,'empCmnt1','2016-04-13')
INSERT INTO #Comments VALUES (61,7,'mgrCmnt1','2016-04-14')
INSERT INTO #Comments VALUES (60,7,'empCmnt2','2016-04-18')
INSERT INTO #Comments VALUES (61,7,'mgrCmnt2','2016-04-19')
INSERT INTO #Comments VALUES (60,8,'empCmnt1','2016-03-01')

INSERT INTO #Plan VALUES (7,'Name1')
INSERT INTO #Plan VALUES (8,'Name2')

INSERT INTO #Employee VALUES (60,61,0,'user1')
INSERT INTO #Employee VALUES (61,null,1,'mgr1')

--Load Temp table with the last comment per employee per plan

CREATE TABLE #LastComment (EmpFK INT, PlanFK INT, Comments VARCHAR(10), ModifiedDate DATE, Mgr BIT)

INSERT INTO #LastComment
    SELECT Comment.EmpFK, Comment.PlanFK, Comment.Comments, Comment.ModifiedDate, Emp.Mgr
    FROM #Comments as Comment
    JOIN (SELECT c.EmpFK, c.PlanFK, MAX(c.ModifiedDate) as MaxCommentDate
        FROM #Comments as c
        GROUP BY EmpFk, PlanFk
        ) as LastComment on Comment.EmpFK = LastComment.EmpFK AND Comment.PlanFK = LastComment.PlanFK AND Comment.ModifiedDate = LastComment.MaxCommentDate
    JOIN #Employee AS Emp on Comment.EmpFK = Emp.EmpPK


SELECT EmpLC.EmpFK, MgrLC.EmpFK, EmpLC.Comments, MgrLC.Comments, EmpLC.ModifiedDate, MgrLC.ModifiedDate, P.PlanFK
FROM #Plan AS P
LEFT JOIN #LastComment AS EmpLC ON P.PlanFK = EmpLC.PlanFK AND EmpLC.Mgr = 0
LEFT JOIN #LastComment AS MgrLC ON P.PlanFK = MgrLC.PlanFK AND MgrLC.Mgr = 1