asked on

Show 1 field from record 2 in record 1.

I have the following 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? Pleasekeep in mind that I could really use some detailed help because most of this code has been thru the help of EE 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, RvCat.ReviewCat, RvCat.NotesDate AS DueDate, 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) AND
(RvCat.NotesDate IS NULL) OR
(RvTyp.ReviewDate BETWEEN @From AND @To) AND (RvCat.ReviewCat = 'RETRN DATE') AND (RvCat.IndID = RvTyp.IndID) AND (RvCat.NotesDate IS NULL) AND
(@Supervisor = ' ALL')
ORDER BY Mgr.Supervisor
Review.xls

Jared_S

There isn't enough information in the query to add a RetrnDate. Can you post the query that generated the second data set as well?

The second data set doesn't include any Return Categories other than Due Date, so I removed the section of the join that included the review category of 'RETRN DATE' .

That's shown here:

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,
RvCat.NotesDate AS DueDate,   
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
   
   (RvCat.IndID = RvTyp.IndID) AND (RvCat.NotesDate IS NULL) AND 
   (@Supervisor = ' ALL')
ORDER BY Mgr.Supervisor

Open in new window

BobRosas

ASKER

Thank you so much for your quick response. Between the odd way data is entered in the table and the example I used (which wasn't very good) I'm afraid I wasn't very clear. We are using a program that we purchased but we have the ability to create reports from the tables. With this program, there was not enough fields for dates so the users added 2 categories/RvCat.ReviewCat (DUE DATE and RETRN DATE) and they entered data (with the datatype of date) in the "Notes" field/Rc.Notes.
So what I'm trying to do (so I can get the report to look right) is...
If the RvCat.ReviewCat is DUE DATE then create a field with the name of "DueDate" and use Rc.Notes (which is a date) as the data. But if the RvCat.ReviewCat is RETRN DATE then create another field called RetrnDate using Rc.Notes as the data.

So this is how the data appears (in part) on the screen where the user enters info...
Category Notes
DUE DATE 08/9/2012
RETRN DATE 09/01/2012

And I want my results to combine the 2 categories into 1. For example...

LastName .....DueDate RetrnDate
Hunz 08/09/2012 09/01/2012

I hope that makes sense. I can really use your help.
Thanks!

Jared_S

I think I understand.
Give this a try and see if it's what you're after.

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 as DueDate
when RvCat.ReviewCat = 'DUE DATE' then RvCat.NotesDate
end,
case as RetrnDate
when RvCat.ReviewCat = 'RETRN DATE' then RvCat.NotesDate
end,

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) AND 
(RvCat.NotesDate IS NULL) OR
(RvTyp.ReviewDate BETWEEN @From AND @To) AND (RvCat.ReviewCat = 'RETRN DATE') AND (RvCat.IndID = RvTyp.IndID) AND (RvCat.NotesDate IS NULL) AND 
(@Supervisor = ' ALL')

ORDER BY Mgr.Supervisor

Open in new window

BobRosas

ASKER

Thank you again! I'm so close! It runs and gives me the fields I want with the data I want but now that I've combined the DueDate record and the RetrnDate record into 1 I'd like to not show both records. If I add the "ReviewCat" field back to my code, and then filter...
CASE WHEN ReviewCat = 'RETRN DATE' (don't show the entire record)
I think that should fix it but how do I do that? I need to filter last after I've gotten data from that record.

ASKER CERTIFIED SOLUTION

Jared_S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BobRosas

ASKER

YES! That's it! I've maxed out points! Thank you so much!

BobRosas

ASKER

What a life saver! Thanks again!