Assistance needed

• irishnut

  Valued Member

  Points: 62

  More actions

  March 7, 2021 at 4:29 pm

  #3854588

  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

   

• Grant Fritchey

  SSC Guru

  Points: 398882

  More actions

  March 8, 2021 at 1:21 pm

  #3854920

  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

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning