Declare @FlightId
Set @FlightId = 151

;With cte As (
	Select
		FLIGHT_NAME = HISTORY_OLD_VALUE,
		RankNum = Row_Number()Over(Partition By FLIGHT_ID, HISTORY_FIELD_NAME Order By HISTORY_TRANSACTION_DATE Desc)
	From ATP_TRANSACTION_HISTORY
)

Select 
	STATUS = 'new'
	FLIGHT_ID,
	FLIGHT_NAME, 
	FLIGHT_EXTRANAME 
From ATP_FA_FLIGHTS
Where FLIGHT_ID = @FlightId

Union

Select 
	STATUS = 'old'
	A.FLIGHT_ID,
	FLIGHT_NAME = isnull(B.FLIGHT_NAME, A.FLIGHT_NAME),
	FLIGHT_EXTRANAME = isnull(C.FLIGHT_EXTRANAME, A.FLIGHT_EXTRANAME)
From ATP_FA_FLIGHTS A
Left Join cte B
On A.FLIGHT_ID = B.HISTORY_TABLE_KEY_ID 
and 'FLIGHT_NAME' = B.HISTORY_FIELD_NAME
and 1 = B.RankNum
Left Join cte C
On A.FLIGHT_ID = C.HISTORY_TABLE_KEY_ID 
and 'FLIGHT_EXTRANAME' = C.HISTORY_FIELD_NAME
and 1 = C.RankNum
Where A.FLIGHT_ID = @FlightId