SELECT Loan.LoanID, Max(A.DateAdded2) AS DateAdded, A.StatusValue
FROM Loan INNER JOIN (SELECT Loan.LoanID,
Max(AdditionalStatus.DateAdded) AS DateAdded2,
AdditionalStatus.StatusValue
FROM Loan INNER JOIN AdditionalStatus ON Loan.LoanID=AdditionalStatus.LoanID
INNER JOIN (SELECT LoanID,
MAX(DateAdded) AS DateAdded
FROM AdditionalStatus
GROUP BY LoanId,
YEAR(DateAdded),
MONTH(DateAdded)) Tbl1
ON AdditionalStatus.LoanId = Tbl1.LoanID AND
AdditionalStatus.DateAdded = Tbl1.DateAdded
WHERE AdditionalStatus.LegalOrWarning=1
GROUP BY Loan.LoanID, AdditionalStatus.DateAdded, AdditionalStatus.StatusValue) A
ON Loan.LoanID=A.LoanID
Group BY Loan.LoanID, A.DateAdded2, A.StatusValue
this should return
001, 4-1-2006, ABC
but is returning
001, 4-1-2006, ABC
001, 3-15-2006, ABC
i want to beable to return just the highest dateadded for each loan where the date is less than a fixed date IE.
WHERE DateAdded < '5-1-2006'
FROM Loan INNER JOIN (SELECT Loan.LoanID,
Max(AdditionalStatus.DateAdded) AS DateAdded2,
AdditionalStatus.StatusValue
FROM Loan INNER JOIN AdditionalStatus ON Loan.LoanID=AdditionalStatus.LoanID
INNER JOIN (SELECT LoanID,
MAX(DateAdded) AS DateAdded
FROM AdditionalStatus
GROUP BY LoanId,
YEAR(DateAdded),
MONTH(DateAdded)) Tbl1
ON AdditionalStatus.LoanId = Tbl1.LoanID AND
AdditionalStatus.DateAdded = Tbl1.DateAdded
WHERE AdditionalStatus.LegalOrWarning=1
GROUP BY Loan.LoanID, AdditionalStatus.DateAdded, AdditionalStatus.StatusValue) A
ON Loan.LoanID=A.LoanID
Group BY Loan.LoanID, A.DateAdded2, A.StatusValue
this should return
001, 4-1-2006, ABC
but is returning
001, 4-1-2006, ABC
001, 3-15-2006, ABC
i want to beable to return just the highest dateadded for each loan where the date is less than a fixed date IE.
WHERE DateAdded < '5-1-2006'