?
Solved

Show 1 field from record 2 in record 1.

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

Expert Comment

by:Jared_S
ID: 38392231
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

0
 

Author Comment

by:BobRosas
ID: 38392344
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!
0
 
LVL 12

Expert Comment

by:Jared_S
ID: 38392492
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

0
Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

 

Author Comment

by:BobRosas
ID: 38392717
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.
0
 
LVL 12

Accepted Solution

by:
Jared_S earned 2000 total points
ID: 38392775
If the report is to only show the due date, this will do it.

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,

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') 

ORDER BY Mgr.Supervisor

Open in new window

0
 

Author Comment

by:BobRosas
ID: 38392794
YES!  That's it!  I've maxed out points!  Thank you so much!
0
 

Author Closing Comment

by:BobRosas
ID: 38392799
What a life saver!  Thanks again!
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

Introduction: This article is aimed at report developers who are used to developing reports using relational databases and have gotten a first-time assignment to develop reports on OLAP cubes. It demonstrates how to build a report using SQL Ser…
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
Integration Management Part 2
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses
Course of the Month15 days, 7 hours left to enroll

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