How do I edit this line to grab the max datevalue:
LEFT JOIN mwlCustomField AS mwlCustomField7 (NOLOCK) ON (mwlLoanApp.ID = mwlCustomField7.LoanApp_ID AND mwlCustomField7.CustFieldDef_ID = '768CA20DB9CF492AB633A919DFC5EA48')
any help is greatly appreciated
jmuslin
Without the context of the query, it is hard to say. Is the date a column in the table mwlCustomField? If it is, are you trying to get just one row that has the max date?
Yes, the date is in a column called DateValue in the table mwlCustomField. If it would help, I can paste the entire stored procedure:
SELECT mwaMWUser.FullName AS [OriginatorName],
mwlBorrower3.LastName AS [Borrower],
MWLLookups.DisplayString AS [Type],
mwlLoanData.LoanProgramName AS [Loan Program],
CASE mwlAppStatus6.StatusDateTime WHEN '1899-12-30 00:00:00.000' THEN NULL ELSE mwlAppStatus6.StatusDateTime END AS [Assigned to Processing],
CASE mwlAppStatus7.StatusDateTime WHEN '1899-12-30 00:00:00.000' THEN NULL ELSE mwlAppStatus7.StatusDateTime END AS [To UW],
CASE mwlCustomField7.DateValue WHEN '1899-12-30 00:00:00.000' THEN NULL ELSE mwlCustomField7.DateValue END AS [Early Lock Expire],
CASE mwlCustomField8.DateValue WHEN '1899-12-30 00:00:00.000' THEN NULL ELSE mwlCustomField8.DateValue END AS [Apprsl Ordered],
CASE mwlCustomField9.DateValue WHEN '1899-12-30 00:00:00.000' THEN NULL ELSE mwlCustomField9.DateValue END AS [Apprsl Rcvd],
CASE mwlCustomField5.DateValue WHEN '1899-12-30 00:00:00.000' THEN NULL ELSE mwlCustomField5.DateValue END AS [Early Lock],
CASE mwlLockRecord.LockExpirationDate WHEN '1899-12-30 00:00:00.000' THEN NULL ELSE mwlLockRecord.LockExpirationDate END AS [Lock Expires],
CASE mwlLoanApp.EstCloseDate WHEN '1899-12-30 00:00:00.000' THEN NULL ELSE mwlLoanApp.EstCloseDate END AS [Est. Closing Date],
mwlAppStatus.StatusDesc AS [Current Status ],
mwaMWUser14.FullName AS [ProcessorName],
mwlAppStatus39.StatusDateTime AS [Pipeline Status Date:To processor]
FROM
mwlLoanApp (NOLOCK)
LEFT JOIN mwaMWUser (NOLOCK) ON (mwlLoanApp.Originator_ID=mwaMWUser.ID)
LEFT JOIN mwlBorrower AS mwlBorrower3 (NOLOCK) ON (mwlLoanApp.ID=mwlBorrower3.LoanApp_ID AND mwlBorrower3.SequenceNum=1)
LEFT JOIN MWLLookups (NOLOCK) ON (mwlLoanApp.TransType = MWLLookups.BOCode AND MWLLookups.ObjectName = 'mwlLoanApp' AND MWLLookups.Fieldname = 'TransType')
LEFT JOIN mwlLoanData (NOLOCK) ON (mwlLoanApp.ID=mwlLoanData.ObjOwner_ID AND mwlLoanData.Active=1)
LEFT JOIN mwlCustomField AS mwlCustomField5 (NOLOCK) ON (mwlLoanApp.ID = mwlCustomField5.LoanApp_ID AND mwlCustomField5.CustFieldDef_ID = 'BAAEF1CD929C4D08A9457B067702EC15')
LEFT JOIN mwlAppStatus AS mwlAppStatus6 (NOLOCK) ON (mwlAppStatus6.LoanApp_Id=mwlLoanApp.ID AND (mwlAppStatus6.StatusDateTime Is Null OR (mwlAppStatus6.StatusDateTime=(SELECT Max(Stat2.StatusDateTime) FROM mwlAppStatus AS Stat2 WHERE (Stat2.LoanApp_Id=mwlAppStatus6.LoanApp_ID AND (ISNULL(Stat2.StatusDesc,'')='Assigned to Processing'))))))
LEFT JOIN mwlAppStatus AS mwlAppStatus7 (NOLOCK) ON (mwlAppStatus7.LoanApp_Id=mwlLoanApp.ID AND (mwlAppStatus7.StatusDateTime Is Null OR (mwlAppStatus7.StatusDateTime=(SELECT Max(Stat2.StatusDateTime) FROM mwlAppStatus AS Stat2 WHERE (Stat2.LoanApp_Id=mwlAppStatus7.LoanApp_ID AND (ISNULL(Stat2.StatusDesc,'')='To underwriting'))))))
LEFT JOIN mwlCustomField AS mwlCustomField7 (NOLOCK) ON (mwlLoanApp.ID = mwlCustomField7.LoanApp_ID AND mwlCustomField7.CustFieldDef_ID = '768CA20DB9CF492AB633A919DFC5EA48')
LEFT JOIN mwlCustomField AS mwlCustomField8 (NOLOCK) ON (mwlLoanApp.ID = mwlCustomField8.LoanApp_ID AND mwlCustomField8.CustFieldDef_ID = 'A2CB30F3377A4AA09EE26F49E66AEB8A')
LEFT JOIN mwlCustomField AS mwlCustomField9 (NOLOCK) ON (mwlLoanApp.ID = mwlCustomField9.LoanApp_ID AND mwlCustomField9.CustFieldDef_ID = '3B224A8413BF4FD1BD96178977CD18EB')
LEFT JOIN mwlLockRecord (NOLOCK) ON (mwlLoanApp.ID=mwlLockRecord.LoanApp_ID AND mwlLockRecord.LockType='INVESTOR' AND mwlLockRecord.Status='CONFIRMED')
LEFT JOIN mwlAppStatus (NOLOCK) ON (mwlAppStatus.LoanApp_Id=mwlLoanApp.ID AND (mwlAppStatus.StatusDateTime Is Null OR (mwlAppStatus.StatusDateTime=(SELECT Max(Stat2.StatusDateTime) FROM mwlAppStatus AS Stat2 WHERE Stat2.LoanApp_Id=mwlAppStatus.LoanApp_ID))))
LEFT JOIN mwaMWUser AS mwaMWUser14 (NOLOCK) ON (mwlLoanApp.Processor_ID=mwaMWUser14.ID)
LEFT JOIN mwlAppStatus AS mwlAppStatus39 (NOLOCK) ON (mwlAppStatus39.LoanApp_Id=mwlLoanApp.ID AND (mwlAppStatus39.StatusDateTime Is Null OR (mwlAppStatus39.StatusDateTime=(SELECT Max(Stat2.StatusDateTime) FROM mwlAppStatus AS Stat2 WHERE (Stat2.LoanApp_Id=mwlAppStatus39.LoanApp_ID AND (ISNULL(Stat2.StatusDesc,'')='To processor'))))))
WHERE
ISNULL(mwlLoanApp.Active,0) = 1
AND
(
(
mwlLoanApp.AppNum NOT LIKE '%T%'
)
AND
(
mwaMWUser14.UserName IN (SELECT mwaMWUser.UserName
FROM mwaUserGroupJoin INNER JOIN
mwaUserGroup ON mwaUserGroupJoin.UserGroup_ID = mwaUserGroup.ID INNER JOIN
mwaMWUser ON mwaUserGroupJoin.MWUser_ID = mwaMWUser.ID
WHERE (mwaUserGroup.Name = 'Processors Level 2') AND (mwaMWUser.ActiveUser = 1) AND (NOT (mwaMWUser.Fullname = 'Administrator')))
)
AND
(
mwlAppStatus.StatusDesc = 'To processor'
OR
mwlAppStatus.StatusDesc = 'Application received'
OR
mwlAppStatus.StatusDesc = 'Review'
OR
mwlAppStatus.StatusDesc = 'To Originator'
OR
mwlAppStatus.StatusDesc = 'To processing supervisor'
OR
mwlAppStatus.StatusDesc = 'To underwriting'
OR
mwlAppStatus.StatusDesc = 'Underwriting to Processor'
OR
mwlAppStatus.StatusDesc = 'Underwritting pending'
OR
mwlAppStatus.StatusDesc = 'Prequalification'
OR
mwlAppStatus.StatusDesc = 'Assigned to Processing'
OR
mwlAppStatus.StatusDesc = 'closing doc to title company'
OR
mwlAppStatus.StatusDesc = 'closing docs drawn'
OR
mwlAppStatus.StatusDesc = 'closing pended'
OR
mwlAppStatus.StatusDesc = 'to closer'
OR
mwlAppStatus.StatusDesc = 'to closing'
)
)
ORDER BY
mwlLoanApp.[ProcessorName],
CONVERT(DATETIME, mwlAppStatus39.[StatusDateTime])
I must admit that I don't know the logic that you want to implement, so this might not get you what you want. If you replace your left join with the following
OUTER APPLY
(
SELECT *
FROM mwlCustomField mwlCustomField7
WHERE mwlLoanApp.ID = mwlCustomField7.LoanApp_ID
AND mwlCustomField7.CustFieldDef_ID = '768CA20DB9CF492AB633A919DFC5EA48'
ORDER BY DateValue DESC
) AS AS mwlCustomField7
that will give you one row for each set where the WHERE clause condition is met.
If that does not get you what you want, post some sample data, and the expected out put.
There should be multiple distinct records but for each record, I need the mwlCustomField.DateValue that was last entered.
Similar to this:
LEFT JOIN mwlAppStatus AS mwlAppStatus7 (NOLOCK) ON (mwlAppStatus7.LoanApp_Id=mwlLoanApp.ID AND (mwlAppStatus7.StatusDateTime Is Null OR (mwlAppStatus7.StatusDateTime=(SELECT Max(Stat2.StatusDateTime) FROM mwlAppStatus AS Stat2 WHERE (Stat2.LoanApp_Id=mwlAppStatus7.LoanApp_ID AND (ISNULL(Stat2.StatusDesc,'')='To underwriting'))))))
In this, there could be multiple Statuses but this Join only pulls the last status that was entered.
I hope that helps
Agggghhhhhhh !!
NOLOCK should never EVER be used in production code.
Hopefully you can educate me as to a need that I am not aware of 
Yea...this code was generated by the Actuate Reporting services. I'm in the process of going through over 200 stored procedures to get rid of it. fun fun! 
Glad to hear you are onto it ...
... no chance of altering a Template or somesuch in Actuate Reporting to get it to output code differently?
You should be table to use the OUTER APPLY example JamesK provided earlier,
SELECT TOP 1 mwlCustomField.DateValue
and add an ORDER BY to get the "last entered" first. I'm not sure if mwlCustomField.DateValue provides the date entered, or if Date Entered is a different column?
We no longer use Actuate and have moved onto SQL Reporting Services
the mwlCustomField.DateValue does have the data I need in the format I need.
thanks for all of the help
I used this:
OUTER APPLY
(
SELECT TOP 1 mwlCustomField7.DateValue
FROM mwlCustomField mwlCustomField7
WHERE mwlLoanApp.ID = mwlCustomField7.LoanApp_ID AND mwlCustomField7.CustFieldDef_ID = '768CA20DB9CF492AB633A919DFC5EA48'
ORDER BY DateValue DESC
) AS mwlCustomField7
in place of this:
EFT JOIN mwlCustomField AS mwlCustomField7 (NOLOCK) ON (mwlLoanApp.ID = mwlCustomField7.LoanApp_ID AND mwlCustomField7.CustFieldDef_ID = '768CA20DB9CF492AB633A919DFC5EA48')
and got the exact same data...only the first mwlCustomField.DateValue was pulled not the last one entered. I changed the DESC to ASC and that made no difference either...
any suggestions?
What does:
SELECT TOP 1 mwlCustomField7.DateValue
FROM mwlCustomField mwlCustomField7
WHERE mwlCustomField7.LoanApp_ID = XXXXX
AND mwlCustomField7.CustFieldDef_ID = '768CA20DB9CF492AB633A919DFC5EA48'
ORDER BY DateValue DESCdisplay if you substitute and actual value for mwlLoanApp.ID at "XXXX"? My guess is that that is not returning the linked data that you are expecting.
nope, it doesn't. It still selects the first date entered, which is kinda weird