Comparing Values in a stored procedure

1

Good Afternoon all.
I am trying to create a stored procedure that can pull the last date entered. The catch is that the last date entered could be in either of 4 different fields, rather than a collection where I could just Max(columnname). While this does get me most of the information I need, it gives me all of the dates, just not the latest one. I thought about going backward, if CustField #4 is null, then check #3. If #3 is Null, check #2, etc...but, I am having difficulty with the syntax.

Any help would be greatly appreciated :smile:
Here's what I have so far:

SELECT  mwlLoanApp.LoanNumber, 
	mwlLoanApp.AppNum, 
	mwlBorrower.LastName, 
	mwlCustomField.DateValue, 
	mwlCustomField.CustFieldDef_ID, 
    mwlLoanApp.OriginatorName, 
    mwlLoanApp.ProcessorName, 
    mwlLoanApp.TransType, 
    mwlLoanData.LoanProgramName, 
    mwlLoanApp.EstCloseDate

FROM mwlLoanApp INNER JOIN
mwlBorrower ON mwlLoanApp.ID = mwlBorrower.LoanAPP_ID INNER JOIN
mwlLoanData ON mwlLoanApp.ID = mwlLoanData.ObjOwner_ID INNER JOIN
mwlCustomField ON mwlLoanApp.ID = mwlCustomField.LoanApp_ID JOIN
(
SELECT LoanApp_ID,
Max(DateValue) as DateValue
FROM mwlCustomField
GROUP BY LoanApp_ID
) AS X
ON mwlCustomField.LoanApp_ID = X.LoanApp_ID
WHERE (mwlBorrower.SequenceNum = '1')
AND (mwlCustomField.CustFieldDef_ID = '768CA20DB9CF492AB633A919DFC5EA48' /CustomField#1/
OR mwlCustomField.CustFieldDef_ID = 'CDCAE4277D10465F8960853976EAE6B6' /CustomField#2/
OR mwlCustomField.CustFieldDef_ID = 'C6B0C4C8B67D4B9589B91104BE7FCF54' /CustomField#3/
OR mwlCustomField.CustFieldDef_ID = '781DED61758C4001B24D56F971E1B4E4') /CustomField#4/

2

It's a little hard to see what you want. Some sample data and desired output would help.

It sounds like you want the maximum date from several date columns. You can do this by adding a CROSS APPLY:

CROSS APPLY
(
    SELECT MAX(dt) FROM (VALUES (dt_Col1), (dt_Col2), ... (dt_Coln)) dates(dt))
) _(maxdt)

and then using the column maxdt in your query

3

I'm assuming the CROSS APPLY would take the place of the SELECT...AS X statement?

4

Yes, I think so.