SQL SERVER 2014 - View value changes depending on the date
It sounds like the Dt_Execution changes, or is updated over time? If so, that will make it difficult to see "new players on a certain date" as there is no history.

In a scenario like this, the "New_Player" attribute should be calculated based on dates, so that either a current or past date could be used.
It may not be appropriate for this table, but you could create another table that has history of Dt_Execution (for weekly games, for example). Then this example table you listed could be compared to that history; if the player's contract date was within a week of the date you select, the procedure or view would calculate that he's a new player.

Without a historical table, this would be difficult or impossible.