SQL Query Last date

George Evans 1 Reputation point
2021-03-07T16:37:05.493+00:00

I have a query where I need only the last date for a dcn. I am unable to get it to work. It pulls all dates. I have posted below the query and highlighted the date I need the last date for. Help needed quickly. Thank you

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,692 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 100.9K Reputation points MVP
    2021-03-07T17:42:55.947+00:00

    Your query is very difficult to read with almost all in uppercase and the SELECT list as a single line. But it seems that the date in question comes from the table CHK_MK_HIST, and no other columns come from this table.

    In such case, replace: 

    Left Join
dbo.CHK_ML_HIST H WITH(NOLOCK) ON R. DCN = H.DCN

    with 

    OUTER APPLY (SELECT TOP (1) h.chk_ml_last_stamp
             FROM   dbo.chk_ml_hist h
             WHERE h.dcn  = r.dcn
             ORDER BY h.chk_ml_time_stamp DESC) AS h
    0 comments
  2. MelissaMa-MSFT 24,176 Reputation points
    2021-03-08T05:50:18.87+00:00

    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.

    0 comments
  3. Guoxiong 8,126 Reputation points
    2021-03-08T22:24:56.82+00:00

    Try this:

    ;WITH CTE_LAST_TOUCH_DATE_by_DCN AS (
    SELECT DCN, MAX(CHK_ML_TIME_STAMP) AS CHK_ML_TIME_STAMP
    FROM dbo.CHK_ML_HIST
    GROUP BY DCN
)

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) AS '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
    CTE_LAST_TOUCH_DATE_by_DCN 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
    R.DCN
    0 comments