If you provide the statements to create the test data, it will be a lot easier to give you some help.

My guess is you need to join on a sub-select of the table with the max(snapshot_date) < requested date.