I would really like some help on this, I'm befuddled. In the table, mwlCustomField, a LoanApp_ID could have multiple rows. What I am trying to do is loop through the rows, find the ones with the same LoanApp_ID, determine which record was last entered by comparing the DateValue, and only return the record that was last entered. This stored procedure only returns one record that has the lastest DateValue. I need this for each record....
Clear as mud?
SELECT mwlLoanApp.AppNum,
mwlLoanApp.OriginatorName,
mwlLoanApp.ProcessorName,
mwlLoanApp.LoanNumber,
mwlBorrower.Fullname,
mwlCustomField.DateValue
FROM mwlLoanApp INNER JOIN
mwlBorrower ON mwlLoanApp.ID = mwlBorrower.LoanAPP_ID INNER JOIN
mwlCustomField ON mwlLoanApp.ID = mwlCustomField.LoanApp_ID
WHERE (mwlCustomField.DateValue =
(SELECT MAX(DateValue) AS Expr1
FROM mwlCustomField AS mwlCustomField_1
WHERE (CustFieldDef_ID = '768CA20DB9CF492AB633A919DFC5EA48') OR
(CustFieldDef_ID = 'CDCAE4277D10465F8960853976EAE6B6') OR
(CustFieldDef_ID = 'C6B0C4C8B67D4B9589B91104BE7FCF54') OR
(CustFieldDef_ID = '781DED61758C4001B24D56F971E1B4E4')))
It seems my stored procedure got broken up, here it is again:
SELECT mwlLoanApp.AppNum, mwlLoanApp.OriginatorName, mwlLoanApp.ProcessorName, mwlLoanApp.LoanNumber, mwlBorrower.Fullname,
mwlCustomField.DateValue
FROM mwlLoanApp INNER JOIN
mwlBorrower ON mwlLoanApp.ID = mwlBorrower.LoanAPP_ID INNER JOIN
mwlCustomField ON mwlLoanApp.ID = mwlCustomField.LoanApp_ID
WHERE (mwlCustomField.DateValue =
(SELECT MAX(DateValue) AS Expr1
FROM mwlCustomField AS mwlCustomField_1
WHERE (CustFieldDef_ID = '768CA20DB9CF492AB633A919DFC5EA48') OR
(CustFieldDef_ID = 'CDCAE4277D10465F8960853976EAE6B6') OR
(CustFieldDef_ID = 'C6B0C4C8B67D4B9589B91104BE7FCF54') OR
(CustFieldDef_ID = '781DED61758C4001B24D56F971E1B4E4')))
SELECT mwlLoanApp.AppNum
,mwlLoanApp.OriginatorName
,mwlLoanApp.ProcessorName
,mwlLoanApp.LoanNumber
,mwlBorrower.Fullname
,q.DateValue
FROM mwlLoanApp
INNER JOIN mwlBorrower
ON mwlLoanApp.ID = mwlBorrower.LoanAPP_ID
INNER JOIN (
SELECT MAX(DateValue) AS Expr1
,LoanApp_ID
FROM mwlCustomField AS mwlCustomField_1
WHERE (CustFieldDef_ID = '768CA20DB9CF492AB633A919DFC5EA48')
OR (CustFieldDef_ID = 'CDCAE4277D10465F8960853976EAE6B6')
OR (CustFieldDef_ID = 'C6B0C4C8B67D4B9589B91104BE7FCF54')
OR (CustFieldDef_ID = '781DED61758C4001B24D56F971E1B4E4')
GROUP BY LoanApp_ID
) q
ON mwlLoanApp.ID = q.LoanApp_ID
If I wanted to grab additional data from the Select(Max(DateValue)...) statement but it is in the same column, DateValue, but with a different row ID, how would I go about doing that?
Ok, in the first Select(Max(DateValue)...) statement, you helped me grab the latest EarlyLockExpire date. Now, I need to grab the latest EarlyLock date. The data is in the same table, uses the same column name, DateValue, but different CustFieldDef_IDs. I need the latest DateValue for both the EarlyLockExpire date and the EarlyLock date.
table design looks like:
ID LoanApp_ID CustFieldDef_ID DateValue
As you can tell, my SQL knowledge is pretty limited.
As a first cut, I would add another join similar to the last one, with the same join condition, but this time pulling out the other date.
Another way is to use a cross-tab approach. Here's a simple example:
declare @ table (a int, b int)
insert into @ (a,b) values (1,2),(3,4),(5,6)
select a
, max(case when a = 1 then b end) as max1
, max(case when a = 2 then b end) as max2
, max(case when a = 3 then b end) as max3
from @
group by a
Like so?
SELECT mwlLoanApp.AppNum,
mwlLoanApp.OriginatorName,
mwlLoanApp.ProcessorName,
mwlLoanApp.LoanNumber,
mwlBorrower.Fullname,
q.EarlyLockExpire,
p.EarlyLock
FROM mwlLoanApp INNER JOIN
mwlBorrower ON mwlLoanApp.ID = mwlBorrower.LoanAPP_ID INNER JOIN
(SELECT MAX(DateValue) AS EarlyLockExpire, LoanApp_ID
FROM mwlCustomField AS mwlCustomField_1
WHERE (CustFieldDef_ID = '768CA20DB9CF492AB633A919DFC5EA48') OR
(CustFieldDef_ID = 'CDCAE4277D10465F8960853976EAE6B6') OR
(CustFieldDef_ID = 'C6B0C4C8B67D4B9589B91104BE7FCF54') OR
(CustFieldDef_ID = '781DED61758C4001B24D56F971E1B4E4')
GROUP BY LoanApp_ID) AS q ON mwlLoanApp.ID = q.LoanApp_ID
INNER JOIN mwlLoanData ON mwlLoanApp.ID = mwlLoanData.ObjOwner_ID
INNER JOIN
(SELECT MAX(DateValue) AS EarlyLock, LoanApp_ID
FROM mwlCustomField AS mwlCustomField_1
WHERE (CustFieldDef_ID = '123') OR
(CustFieldDef_ID = '456') OR
(CustFieldDef_ID = '789') OR
(CustFieldDef_ID = '012')
GROUP BY LoanApp_ID) AS p ON mwlLoanApp.ID = p.LoanApp_ID