0

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

2
  • Is there any designation in the employee table of who is a manger? Is there more than one manager? W need more detail here.
    – tpet
    Apr 19, 2016 at 18:15
  • Edited the question @tpet
    – Jil
    Apr 19, 2016 at 18:18

1 Answer 1

1

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
4
  • Hi tpet, Im new to T-SQL and Im confused as to why you assigned two aliases on one particular table. Can you provide a reference on where this can be used? thanks Apr 19, 2016 at 20:09
  • 1
    Hi @rk13, both #Employee and #Comments are each used and aliased twice because there is a self join for #Employee to return the Manager - Employee relationship. Then since both employees and managers have comments, #Comments is used twice.
    – tpet
    Apr 19, 2016 at 20:13
  • @tpet My bad.. I did not mention the schema of the tables used. Your answer did point me in a good direction but I missed out to mention a point. The latest employee and manger comments for each plan is the expected result. That would be a group by on the plan ? I am new to this and in the learning stage.
    – Jil
    Apr 20, 2016 at 5:10
  • Does the updated query work for you?
    – tpet
    Apr 21, 2016 at 18:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.