Extract data from a historical table keeps only data with changes

I have a table like this

and here is the log table

So as you understood, my main table contains data from a source every day. But keeps only those with changes. Thus if everyday if 100k records comming normally, but very few (<100) are really has some changes. Thus my table tracks all the records using the log ID

So when I required to extract latest data or for any date this query works well

select * from (select pcode,description,T.logid,extractiondate,dense_rank() 
over(partition by pcode order by extractiondate desc) rn from @temp  T 
inner join @logs L on L.logid=T.logid where 
L.extractiondate<=(select max(extractiondate) from @logs))tbl where rn=1
 order by extractiondate desc

So if I specify a date instead of max, I can get that days data. there is no issue.

But I have got one more requirement. Instead of date if I specify LogId, how can I achieve the same using logId.

I know I can work it using getting the Date corresponding to a log Id and supply it to my above query. But I am trying to get the same in a better way using LogId. Please help

Here is the fiddle

Answer

Were you attempting to do this?

select *
  from (
       select pcode, description, T.logid, extractiondate
            , dense_rank() over (partition by pcode order by extractiondate desc) rn
         from temp  T
         join logs  L
           on L.logid = T.logid
        where L.extractiondate <= (select MAX(extractiondate) from logs WHERE logid = 8)
       ) tbl
 where rn = 1
 order by extractiondate desc
;

Note: We don’t need MAX if logid is unique in logs.

See: Test case adjusted