Join Two Tables with get value AsssetCode from Max Date

Question

I'm trying join Two tables:

KDSCMSCNTRCTD
-----------------
CNTRCTDID | CNTRCTDCOMP | CNTRCTDLNUM |
--------------------------------------
 3221 | NAV     | 207
--------------------------------------



KDSCMSCNTRCTDHIST
----------------------------------------------
CNTRCTDHISTID | CNTRCTDHISTSDATE | CNTRCTDHISTCOMP | CNTRCTDHISTLNUM | CNTRCTDHISTASSCODE |
------------------------------------------------------------------------------------------
  65 |  17-NOV-21 | NAV      | 207           | 00091
------------------------------------------------------------------------------------------
  66 |  20-NOV-21 | NAV      | 207           | 123
------------------------------------------------------------------------------------------

i need only get AssetCode from (max) CNTRCTDHISTSDATE from KDSCMSCNTRCTD Like this:

CNTRCTDID | CNTRCTDCOMP | CNTRCTDLNUM | CNTRCTDHISTASSCODE |
-----------------------------------------------------------
 3221 | NAV     | 207 | 123
-----------------------------------------------------------

select CNTRCTDLNUM as PRODID,
CNTRCTDHISTASSCODE as ASSET  
from KDSCMSCNTRCTD left join KDSCMSCNTRCTDHIST ON CNTRCTDHISTLNUM = CNTRCTDLNUM   
where CNTRCTDLNUM = :CONTRACTCODE and CNTRCTDCOMP = :Company ;

Answer 1

Try a compact query too:

select *, 
    CNTRCTDHISTASSCODE = (select top(1) CNTRCTDHISTASSCODE from KDSCMSCNTRCTDHIST where CNTRCTDCOMP = t.CNTRCTDCOMP and CNTRCTDHISTLNUM = t.CNTRCTDLNUM order by CNTRCTDHISTSDATE desc)
from KDSCMSCNTRCTD t
where CNTRCTDLNUM = :CONTRACTCODE and CNTRCTDCOMP = :Company

Answer 2

Try the approach that uses joins:

select 
   t.CNTRCTDID, t.CNTRCTDCOMP, t.CNTRCTDLNUM, h.CNTRCTDHISTASSCODE
from KDSCMSCNTRCTD t
left join KDSCMSCNTRCTDHIST h ON h.CNTRCTDHISTLNUM = t.CNTRCTDLNUM and h.CNTRCTDHISTCOMP = t.CNTRCTDCOMP
left join KDSCMSCNTRCTDHIST h2 ON h2.CNTRCTDHISTLNUM = t.CNTRCTDLNUM and h2.CNTRCTDHISTCOMP = t.CNTRCTDCOMP and h2.CNTRCTDHISTSDATE > h.CNTRCTDHISTSDATE
where t.CNTRCTDLNUM = :CONTRACTCODE and t.CNTRCTDCOMP = :Company
and h2.CNTRCTDHISTID is null

Answer 3

Hi @Riyn Pratama ，
Welcome to the microsoft TSQL Q&A forum!

Please check:

    CREATE TABLE #KDSCMSCNTRCTD(CNTRCTDID INT,CNTRCTDCOMP VARCHAR(15),CNTRCTDLNUM INT)  
    INSERT INTO #KDSCMSCNTRCTD VALUES(3221,'NAV',207)  
      
    CREATE TABLE #KDSCMSCNTRCTDHIST( CNTRCTDHISTID INT,CNTRCTDHISTSDATE DATE,CNTRCTDHISTCOMP VARCHAR(15),   
     CNTRCTDHISTLNUM INT,CNTRCTDHISTASSCODE VARCHAR(15))  
    INSERT INTO #KDSCMSCNTRCTDHIST VALUES(65,'17-NOV-21','NAV', 207,'00091'),  
       (66,'20-NOV-21','NAV',207,'123')  
      
    SELECT * FROM  #KDSCMSCNTRCTD  
    SELECT * FROM  #KDSCMSCNTRCTDHIST     
      
    ;with cte  
    as(select *,max(CNTRCTDHISTSDATE) over(partition by CNTRCTDID) mm  
    from #KDSCMSCNTRCTD k  
    left join #KDSCMSCNTRCTDHIST  
    ON CNTRCTDHISTLNUM = CNTRCTDLNUM     
    where CNTRCTDLNUM =CNTRCTDHISTLNUM and CNTRCTDCOMP =CNTRCTDHISTCOMP)  
      
    select CNTRCTDID,CNTRCTDCOMP,CNTRCTDLNUM,CNTRCTDHISTASSCODE  
    from cte  
    where CNTRCTDHISTSDATE=mm  

Or:

 select CNTRCTDID,CNTRCTDCOMP,CNTRCTDLNUM,CNTRCTDHISTASSCODE  
 from #KDSCMSCNTRCTD k  
 left join (SELECT *,max(CNTRCTDHISTSDATE) over(order by CNTRCTDHISTCOMP) mm  
 FROM  #KDSCMSCNTRCTDHIST) t  
 ON CNTRCTDHISTLNUM = CNTRCTDLNUM and CNTRCTDLNUM =CNTRCTDHISTLNUM   
 and CNTRCTDCOMP =CNTRCTDHISTCOMP  
 where CNTRCTDHISTSDATE=mm  

Output:

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards,
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
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.

Answer 4

Hi @Riyn Pratama ，

Try:

     CREATE TABLE #KDSCMSCNTRCTD(CNTRCTDID INT,CNTRCTDCOMP VARCHAR(15),CNTRCTDLNUM INT)  
     INSERT INTO #KDSCMSCNTRCTD VALUES(142,'NAV',207),(143,'AAV',208)  
          
     CREATE TABLE #KDSCMSCNTRCTDHIST( CNTRCTDHISTID INT,CNTRCTDHISTSDATE DATE,CNTRCTDHISTCOMP VARCHAR(15),   
      CNTRCTDHISTLNUM INT,CNTRCTDHISTASSCODE VARCHAR(15))  
     INSERT INTO #KDSCMSCNTRCTDHIST VALUES(142,'17-NOV-21','NAV', 207,'00091'),  
        (142,'20-NOV-21','NAV',207,'123'),(143,'20-NOV-21','AAV',208,'123'),  
 (143,'17-NOV-21','AAV',208,'444')  
          
     SELECT * FROM  #KDSCMSCNTRCTD  
     SELECT * FROM  #KDSCMSCNTRCTDHIST   
  
  select CNTRCTDID,CNTRCTDCOMP,CNTRCTDHISTASSCODE  
  from #KDSCMSCNTRCTD k  
  left join (SELECT *,max(CNTRCTDHISTSDATE) over(order by CNTRCTDHISTCOMP) mm  
  FROM  #KDSCMSCNTRCTDHIST) t  
  ON CNTRCTDHISTLNUM = CNTRCTDLNUM and CNTRCTDLNUM =CNTRCTDHISTLNUM   
  and CNTRCTDCOMP =CNTRCTDHISTCOMP  
  where CNTRCTDHISTSDATE=mm  

Output：

Echo