Assistance needed

  • I have the need to pull data using a last touched date. Below ins the query, I highlighted the date I need , it pulls all dates. Please help.

    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', 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_HIST H WITH(NOLOCK) ON R. DCN = H.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

     

  • What about using TOP 1 and order by the 'Last touch date'?

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply