Combine 2 records into one.

I posted a similiar question and thought it was solved but the code always returned a Null "RetrnDate".  I tweaked the code so it shows all data but I can't get the 2 records combined again.  I'd like to add the "Return Date" from another record to the "Due Date" record.

I attached the code which displays data as shown in the attached spreadsheet.  How can I change the code so it looks like the 2nd set of data?  Please keep in mind that I could really use some detailed help because most of this code has been thru the help of EE (Thank You!) and I'm not sure how to tear it apart.

SELECT DISTINCT
                         RvCat.IndID, Ind.LastName, Ind.FirstName + ' ' + CASE WHEN Ind.MiddleInit IS NULL THEN '' ELSE '' + ind.MiddleInit END AS FirstName, Ind.Manager AS ManagerID,
                         Ind.LastStartDate, RvTyp.Notes, Ind.DeptID, RvTyp.ReviewType, CASE WHEN RvCat.ReviewCat = 'DUE DATE' THEN RvCat.NotesDate END AS DueDate,
                         CASE WHEN RvCat.ReviewCat = 'RETRN DATE' THEN RvCat.NotesDate END AS RetrnDate, Mgr.Supervisor, RvCat.ReviewCat
FROM            (SELECT        Rc.IndID, Rc.ReviewDate, Rc.ReviewCat, Rc.Notes AS NotesDate
                          FROM            tblHRIndReviewbyCategory AS Rc RIGHT OUTER JOIN
                                                    tblHRIndReview AS Rv ON Rc.IndID = Rv.IndID) AS RvCat RIGHT OUTER JOIN
                         tblHRIndReview AS RvTyp ON RvCat.ReviewDate = RvTyp.ReviewDate AND RvCat.IndID = RvTyp.IndID RIGHT OUTER JOIN
                         vw_HrIndividuals AS Ind INNER JOIN
                         vw_HrLkUpSupervisor AS Mgr ON Ind.Manager = Mgr.SupervisorID ON RvTyp.IndID = Ind.IndID
WHERE        (RvTyp.ReviewDate BETWEEN @From AND @To) AND (Ind.Manager = @Supervisor) AND (RvCat.ReviewCat = 'DUE DATE') AND (RvCat.IndID = RvTyp.IndID) OR
                         (RvTyp.ReviewDate BETWEEN @From AND @To) AND (RvCat.ReviewCat = 'DUE DATE') AND (RvCat.IndID = RvTyp.IndID) AND (@Supervisor = ' ALL') OR
                         (RvTyp.ReviewDate BETWEEN @From AND @To) AND (Ind.Manager = @Supervisor) AND (RvCat.ReviewCat = 'RETRN DATE') AND (RvCat.IndID = RvTyp.IndID) OR
                         (RvTyp.ReviewDate BETWEEN @From AND @To) AND (RvCat.ReviewCat = 'RETRN DATE') AND (RvCat.IndID = RvTyp.IndID) AND (@Supervisor = ' ALL')
ORDER BY Mgr.Supervisor
Reviews.xls
BobRosasAsked:
Who is Participating?
 
Nico BontenbalCommented:
I think this is what you need:
http://msdn.microsoft.com/en-us/library/ms177673.aspx

Using that technique you get something like:
SELECT 
    RvCat.IndID, 
    Ind.LastName, 
    Ind.FirstName + ' ' + CASE WHEN Ind.MiddleInit IS NULL THEN '' ELSE '' + ind.MiddleInit END AS FirstName, 
    Ind.Manager AS ManagerID, 
    Ind.LastStartDate, 
    RvTyp.Notes, 
    Ind.DeptID, 
    RvTyp.ReviewType, 
    max(CASE WHEN RvCat.ReviewCat = 'DUE DATE' THEN RvCat.NotesDate END) AS DueDate, 
    max(CASE WHEN RvCat.ReviewCat = 'RETRN DATE' THEN RvCat.NotesDate END) AS RetrnDate,
    Mgr.Supervisor
FROM            (SELECT        Rc.IndID, Rc.ReviewDate, Rc.ReviewCat, Rc.Notes AS NotesDate
                          FROM            tblHRIndReviewbyCategory AS Rc RIGHT OUTER JOIN
                                                    tblHRIndReview AS Rv ON Rc.IndID = Rv.IndID) AS RvCat RIGHT OUTER JOIN
                         tblHRIndReview AS RvTyp ON RvCat.ReviewDate = RvTyp.ReviewDate AND RvCat.IndID = RvTyp.IndID RIGHT OUTER JOIN
                         vw_HrIndividuals AS Ind INNER JOIN
                         vw_HrLkUpSupervisor AS Mgr ON Ind.Manager = Mgr.SupervisorID ON RvTyp.IndID = Ind.IndID
WHERE        (RvTyp.ReviewDate BETWEEN @From AND @To) AND (Ind.Manager = @Supervisor) AND (RvCat.ReviewCat = 'DUE DATE') AND (RvCat.IndID = RvTyp.IndID) OR
                         (RvTyp.ReviewDate BETWEEN @From AND @To) AND (RvCat.ReviewCat = 'DUE DATE') AND (RvCat.IndID = RvTyp.IndID) AND (@Supervisor = ' ALL') OR
                         (RvTyp.ReviewDate BETWEEN @From AND @To) AND (Ind.Manager = @Supervisor) AND (RvCat.ReviewCat = 'RETRN DATE') AND (RvCat.IndID = RvTyp.IndID) OR
                         (RvTyp.ReviewDate BETWEEN @From AND @To) AND (RvCat.ReviewCat = 'RETRN DATE') AND (RvCat.IndID = RvTyp.IndID) AND (@Supervisor = ' ALL')
GROUP BY
    RvCat.IndID, 
    Ind.LastName, 
    Ind.FirstName + ' ' + CASE WHEN Ind.MiddleInit IS NULL THEN '' ELSE '' + ind.MiddleInit END, 
    Ind.Manager, 
    Ind.LastStartDate, 
    RvTyp.Notes, 
    Ind.DeptID, 
    RvTyp.ReviewType, 
    Mgr.Supervisor
ORDER BY Mgr.Supervisor

Open in new window


I couldn't test it of course because I don't have your data, but I suppose you get the idea. Using group by instead of distinct gives you greater control over the output.
0
 
BobRosasAuthor Commented:
Yes!  Thank you!  
I pasted in the code and it just ran.  I did some tests and it appears to be working great.  Thank you again for the detailed code.  I've maxed out your points.  This really helped me out a lot.
0
 
BobRosasAuthor Commented:
Great help!  Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.