>I am confused. Table b is the table with status and datetime stamp.

One possibility then:

select distinct a.PartNo 
from Table a inner join TableB B1 on a.PartNo = B1.PartNo
 where B1.Status = 2 and B1.DateTime = (select max(DateTime) from Tableb b where a.PartNo = b.PartNo)

Same using EXISTS:

select a.PartNo from TableA a where a.PartNo = (select top 1 PartNo from TableB order by Date desc) and
exists (select 1 from TableB b where a.PartNo =  b.PartNo and b.Status  =2)