We help IT Professionals succeed at work.

How do i select the highest date within an ID when using multiple joins

NO_worries
NO_worries asked
on
230 Views
Last Modified: 2011-10-19
Iam using Reporting Services with 3 tables joined :
I want to select the highest IRR_LVR  date by loanid ie. For every loanid there are a number of dates
But I only require the highest date.I tried using max but does not seem to work   can I do this : please see attached tables and query

Comment
Watch Question

Commented:
I can't see your attached query. Did you attach something?
But anyway, try this:
SELECT IRR_LVR
FROM .....
WHERE ....
AND IRR_LVR = (SELECT TOP 1 IRR_IVR FROM ... ORDER BY IRR_IVR DESC)

Author

Commented:
yes i did attach a word doc file giving table but here is the query



SELECT LoanMaster.LoanID, SUM(LoanTransaction.Value) AS Expr1, LoanMaster.Principle, LoanMaster.ProjectName, IRR_LVR.IRR, IRR_LVR.LVR

FROM LoanMaster INNER JOIN
LoanTransaction ON LoanMaster.LoanID = LoanTransaction.LoanID INNER JOIN
IRR_LVR ON LoanMaster.LoanID = IRR_LVR.LoanID

WHERE (LoanTransaction.Date <= @EndDate) AND (NOT (LoanTransaction.TransactionType LIKE N'ESTAC%'))

GROUP BY LoanMaster.LoanID, LoanMaster.Principle, LoanMaster.ProjectName, IRR_LVR.IRR, IRR_LVR.LVR, IRR_LVR.DateOfChange


I-want-to-select-the-highest-IRR.doc

Commented:
Try this:

SELECT LoanMaster.LoanID, SUM(LoanTransaction.Value) AS Expr1, LoanMaster.Principle, LoanMaster.ProjectName, IRR_LVR.IRR, IRR_LVR.LVR,
(SELECT TOP 1 IRR_LVR.DateOfChange FROM IRR_LVR WHERE LoanMaster.LoanID = IRR_LVR.LoanID ORDER BY IRR_LVR.DateOfChange DESC) AS Expr2
FROM LoanMaster INNER JOIN
LoanTransaction ON LoanMaster.LoanID = LoanTransaction.LoanID INNER JOIN
...
(You may have to change something in GROUP BY and ORDER BY statement)

Author

Commented:
Its is selecting the highest date but the value off IRR  changes. Pls look at query : and output data:

SELECT LoanMaster.loanid, LoanTransaction.Loanid,
LoanTransaction.date, LoanTransaction.value,

(SELECT TOP 1 IRR_LVR.DateOfChange FROM IRR_LVR WHERE LoanMaster.LoanID = IRR_LVR.LoanID

ORDER BY IRR_LVR.DateOfChange DESC) AS Expr2, IRR_LVR.IRR, IRR_LVR.LVR
From LoanMaster

INNER JOIN
LoanTransaction ON LoanMaster.LoanID = LoanTransaction.LoanID
Inner JOIN
IRR_LVR ON LoanMaster.LoanID = IRR_LVR.LoanID
group by LoanMaster.loanid, IRR_LVR.DateOfChange,LoanTransaction.Loanid, LoanTransaction.date,
LoanTransaction.value, IRR_LVR.IRR,IRR_LVR.LVR
order by LoanMaster.loanid

output file

I ave attach a file with the results


docsnd1.doc
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Hi thanks for your help its perfect

Author

Commented:
Thanks for your help - it works perfect
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.