Understanding Actual Termination Date and Per_Periods_Of_Service

Summary:

Looking for SQL to pull in an accurate termination date given a point in time. And if that point in time is BEFORE the termination, then the business is wanting a blank value for that cell.

Content (required):

The business is asking for a report which outlines a point in time snapshot of the company's employees. So basically, "given a date, show me all the stats on that employee as of that date." One column in the report is the "Actual Termination Date" in the per_periods_of_service table. The behavior I'm noticing in this table is that when a person is termed, it doesn't look like a new row gets inserted into the per_periods_of_service table, into which this termination date gets recorded, leaving the old row with a blank term date. Instead,