SQL SERVER 2014 - View value changes depending on the date In a Data Mart I have a table with information about Players (Dimension Player). And It have the following attributes: 


Name (nvarchar(250))
Dt_Contract (date)
New_Player (int)
DT_Execution (datetime)


The New_Player is a “binary code” and it show us the new team players. It is calculated as follows:
MONTH(DT_Contract) = MONTH(CAST(@DT_Execution AS DATE)) THEN 1 ELSE 0 END
So,

1 – Is a new player

0 – Is a old player
The Dt_Execution is  the Date on which the process runs



I liked to be able to see what new players on a specific date e not only when Month(DT_Conttract) = Month(Dt_Execution)

Basically, 
Players who have been entered today are with the correct value in New_Player but if I want to observe what were the new players last month I will not be able to watch what they were. I think I need something more dynamic paw achieve this flexibility.

Does anyone have any idea how can I get it? What type of modification I have to implement in the table?

PS: The code is inserted SP that runs every day.
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.