SQLTeam.com | Weblogs | Forums

Field Associated with Subquery


I am fairly certain that I'm overthinking this.

I am getting the correct ah.EntryDate with this subquery:
(Select Max(Case When ah.PrincipalAmount < -1000 Then ah.EntryDate Else '01/01/1901' End) From AccountHistory ah Where ah.MemberNumber = ln.MemberNumber AND ah.AccountNumber = ln.LoanNumber)

I am trying to get the associated ah.PrincipalAmount of the transaction with this ah.EntryDate.

I can't quite seem to come up with a solution that gets me what I am after. Any ideas? Thank you.

ISNULL((Select Top (1) ah.PrincipalAmount From AccountHistory ah Where ah.PrincipalAmount < -1000 AND ah.MemberNumber = ln.MemberNumber AND ah.AccountNumber = ln.LoanNumber order by ah.EntryDate DESC), '19010101')

Holy cow. It's like you were waiting for my question!!!

It worked. Thank you very much.

You're welcome!