Select
*
From [dbo].[tbl_TrackingNotes]
Where FK_OrderNo In (
select
PK_OrderNo
From tbl_ShipmentHeader SH
Left outer join [dbo].[tbl_ShipmentMilestone] SM ON SM.FK_OrderNo = SH.PK_OrderNo
Where Housebill in (
4520798,
4529037,
4523647,
4519400,
4526763,
4518052,
4514089,
4526496,
4519378,
4523597,
4524559,
4526638,
4517635) 
And SM.FK_OrderStatusId in ('REF', 'RD1', 'CXL', 'REF', 'DEL', 'RAI')
And Year(SM.EventDateTime) = 1900
)
And
PK_NoteNo In (Select
PK_NoteNo
From [dbo].[tbl_TrackingNotes]
Where Year(EventDateTime) = 1900)
Order By DateTimeEntered desc