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