Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Not returning max date

Status
Not open for further replies.

jgurgen

Programmer
Mar 9, 2006
192
US
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'
 
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:
Code:
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
 
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]
 
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.

Code:
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
 
ok i think this would work but i keep getting syntax error near keyword ON

why?

Code:
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
 
You need to alias your derived table:
Code:
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
 
Lamprey13 that doesn't work, it still returns mutliple dates for each loanid.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top