Not returning max date

[jgurgen]

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'

 

[MikeBronner]

Verify that your results are indeed what you think they are. Check the ABC or 001 values for spaces around the text. Use LTRIM and RTRIM to get clean results:

SELECT Loan.LoanID
	,Max(A.DateAdded2) AS DateAdded
	,A.StatusValue
FROM Loan
	INNER JOIN (SELECT Loan.LoanID
			,Max(AdditionalStatus.DateAdded) AS DateAdded2
			,LTRIM(RTRIM(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
			,LTRIM(RTRIM(AdditionalStatus.StatusValue))) A ON Loan.LoanID=A.LoanID
Group BY Loan.LoanID
	,A.DateAdded2
	,A.StatusValue

Take Care,
Mike

 

[ESquared]

Commas on the next line... it kills me. Sorry, I had to say it.

jgurgen,

Run each derived table by itself... is it returning what you would expect?

[COLOR=black #d0d0d0]When I walk, I sometimes bump into things. I am closing my eyes so that the room will be empty.[/color]

 

[jgurgen]

Basically this sql statement is returning all the results i need. From here i need to take the max(dateadded) for each LoanID where the date is less then a specified date from @EOM_Date.

SELECT AdditionalStatus.LoanID,
	AdditionalStatus.DateAdded,
	AdditionalStatus.StatusValue
FROM AdditionalStatus	
	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 LegalOrWarning = 1

 

[jgurgen]

ok i think this would work but i keep getting syntax error near keyword ON

why?

SELECT AdditionalStatus.LoanID, Max(AdditionalStatus.DateAdded) as DateAdded, AdditionalStatus.StatusValue
FROM AdditionalStatus INNER JOIN (SELECT Distinct LoanID
				FROM R_IFS_AdditionalStatus)
			ON AdditionalStatus.LoanID = R_IFS_AdditionalStatus.LoanID
Group By AdditionalStatus.LoanID, AdditionalStatus.DateAdded, AdditionalStatus.StatusValue

 

[Lamprey13]

You need to alias your derived table:

SELECT 
	AdditionalStatus.LoanID, 
	Max(AdditionalStatus.DateAdded) as DateAdded, 
	AdditionalStatus.StatusValue
FROM 
	AdditionalStatus 
INNER JOIN 
	(
		SELECT Distinct LoanID
		FROM R_IFS_AdditionalStatus
	) [COLOR=red]AS R_IFS_AdditionalStatus[/color]
	ON AdditionalStatus.LoanID = R_IFS_AdditionalStatus.LoanID
GROUP BY 
	AdditionalStatus.LoanID, 
	AdditionalStatus.DateAdded, 
	AdditionalStatus.StatusValue

 

[jgurgen]

Lamprey13 that doesn't work, it still returns mutliple dates for each loanid.