Hi @George Evans ,
Welcome to Microsoft Q&A!
For this type of problem we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.
In addition, please also refer below and check whether it is working:
SELECT R.DCN,CHK_ML_CLIENT_CODE,CONVERT(VARCHAR(10),R.SCANNED_DATE,101) AS 'SCANNED DATE',
R.CHK_ML_CUR_STATUS,R.CHK_ML_QUEUE,R.READ_ONLY_INDICATOR,
--CONVERT(VARCHAR(10),H.CHK_ML_TIME_STAMP,101)AS 'LAST TOUCH DATE',
(select top 1 CONVERT(VARCHAR(10),CHK_ML_TIME_STAMP,101) from dbo.CHK_ML_HIST H WITH(NOLOCK) where H.DCN = R.DCN order by h.chk_ml_time_stamp DESC) AS 'LAST TOUCH DATE',
R.CHK_ML_LAST_TOUCH,CONVERT(VARCHAR(10),R.CHK_ML_LOAD_DATE,101)AS 'LOAD DATE',R.CHK_ML_FACETS_CHK_NO, R.CHK_ML_PROV_NAME,R.CHK_ML_CHECK_AMT,CONVERT(VARCHAR(10),R.CHK_ML_LOCK_BOX_DT,101)AS 'LOCK BOX DATE',R.READ_ONLY_INDICATOR,R.RECLASS_RECD_CHECK_AMT,R.CHK_ML_OPS_CHECK_REASON,R.CHK_ML_PRPR_NAME,R.CHK_ML_PRPR_ADDRESS,CONVERT(VARCHAR(10),R.CHK_ML_RECD_DT,101)AS 'RECEIVED DATE',CONVERT(VARCHAR(10),R.REOPEN_DT,101)AS 'REOPENED DATE',R.REOPEN_USER,CONVERT(VARCHAR(10),R.REOPEN_RQST_DT,101)AS 'REOPEN REQUEST DATE',RIGHT(LEFT(R.DCN,12),2) 'DOCTYPE'
FROM
dbo.CHK_ML_RECD R WITH(NOLOCK) LEFT JOIN
dbo.OPS_CHECK_FILE O WITH(NOLOCK) ON R.DCN = O.DCN Left Join
dbo.CHK_ML_RECD_CHK_CLCL_INFO C ON R.DCN = C.DCN
WHERE (R.CHK_ML_CLIENT_CODE = 'BTX' )
AND R.CHK_ML_CUR_STATUS ='11'
AND RIGHT(LEFT(R.DCN,12),2)<> '13'
ORDER BY DCN
Best regards
Melissa
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.