Nobody knows what your tables look like or your data looks like. We also don't know the requirements. There is absolutely no way anybody has chance at answering your question unless you provide some details. Take a look at the first link in my signature about best practices when posting questions.

I took the liberty of formatting your query so people can read it. If you use the IFCode shortcuts (over on the left when posting), you can wrap your code in these blocks and it will maintain the formatting. 🙂

SELECT c.cts_app_fkey AS APP_NUMBER

,a.CREDIT_AMOUNT

,REPLACE(d.DLR_REFERENCE_NUM, '.', '') AS DEALER_NUM

,c.cts_contract_number AS APP_CONTRACT_NUMBER

,CASE co.CO_UD_APP_STATUS_TBDESC

WHEN 'PO Issued'

THEN CASE a.APP_DECISION_CODE

WHEN 7

THEN 'Booked'

ELSE 'PO Issued'

END

WHEN 'Approved'

THEN CASE co.CO_LESSOR

WHEN '421'

THEN 'Approved - SFP'

WHEN '423'

THEN 'Approved - SFP'

WHEN '424'

THEN 'Approved - SFP'

ELSE 'Approved'

END

ELSE co.CO_UD_APP_STATUS_TBDESC

END AS APPLICATION_STATUS

,CONVERT(VARCHAR, co.CO_BOOKING_DATE, 101) AS BOOKING_DATE

,CONVERT(XML, a.APP_DATA, 0).value('(//CUST.NAME/node())[1]', 'VARCHAR(50)') AS CUSTOMER

,CONVERT(XML, a.APP_DATA, 0).value('(//DBA/node())[1]', 'VARCHAR(50)') AS CUSTOMER_DBA

,CASE

WHEN LEN(CONVERT(XML, a.APP_DATA, 0).value('(//CUST.CREDIT.ACCT/node())[1]', 'VARCHAR(50)')) < 8

THEN CONVERT(XML, a.APP_DATA, 0).value('(//CUST.CREDIT.ACCT/node())[1]', 'VARCHAR(50)')

ELSE NULL

END AS CCAN

,CONVERT(VARCHAR, CONVERT(DATE, CONVERT(XML, a.APP_DATA, 0).value('(//UD_DATE_ENTERED/node())[1]', 'VARCHAR(10)')), 101) AS DATE_ENTERED

,CONVERT(XML, a.APP_DATA, 0).value('(//FED.ID/node())[1]', 'VARCHAR(50)') AS FEDERAL_ID

FROM dbo.APPLICATION AS a

LEFT JOIN dbo.RPT_CO AS co ON a.APP_KEY = co.APP_FKEY

LEFT JOIN dbo.DEALER AS d ON CONVERT(XML, a.APP_DATA, 0).value('(//DEALER/node())[1]', 'VARCHAR(50)') = d.DLR_KEY

LEFT JOIN dbo.Contract_Setup AS c ON a.app_key = c.cts_app_fkey

WHERE (DATEDIFF(MINUTE, c.CTS_LAST_SAVED_DATE, GETDATE()) < 50)

AND (DATEDIFF(MINUTE, c.CTS_LAST_SAVED_DATE, GETDATE()) > 30)