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.

1 Answer 1


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.