>>But with the second set of data i am not sure how NOT to capture the record where the salary was changed!

To solve this problem first write a cursor to identify employee id's whose salray was changed.

Cursor v_cur is
SELECT ejheeid
FROM dbo.emphjob
WHERE IsRateChange = 'Y'
AND DateTimeCreated between fromdate and todate



Can I UNION 2 sets of data - one with new rate / salary where isratechange = y and one with MAX(dateTimeCreated)?

You can use union.