?
Solved

Combine 2 records into one.

Posted on 2012-09-12
3
Medium Priority
?
416 Views
Last Modified: 2012-09-13
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
0
Comment
Question by:BobRosas
  • 2
3 Comments
 
LVL 22

Accepted Solution

by:
Nico Bontenbal earned 2000 total points
ID: 38394011
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
 

Author Comment

by:BobRosas
ID: 38396348
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
 

Author Closing Comment

by:BobRosas
ID: 38396350
Great help!  Thank you!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Written by Valentino Vranken. A while ago I wrote an article called Chart Optimization Tips (http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Chart-Optimization-Tips.html).  This article explained how …
Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question