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.
13,343 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,599 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Erland Sommarskog 106.8K Reputation points
    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 No comments

  2. MelissaMa-MSFT 24,186 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 No comments

  3. Guoxiong 8,201 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 No comments