January 5, 2016 at 9:04 am
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.
January 5, 2016 at 10:36 am
antonio_scp125 (1/5/2016)
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.
What does DT_Contract represent?
January 5, 2016 at 11:39 am
The beginning of the contract
January 5, 2016 at 11:52 am
antonio_scp125 (1/5/2016)
The beginning of the contract
Please bear in mind that I have no idea about your data. I do not know what your contracts are. I don't know what 'players' are. Try to put yourself in the position of an outsider when writing your posts here.
Is DT_Contract the date on which a player became a new player?
Because what you have described sounds too easy. Let's assume that your 'specific date' is '20160101'.
Your WHERE clause would be
Where DT_Contract >= '20160101' and DT_Contract < '20160102'
January 5, 2016 at 12:09 pm
SSCertifiable thanks for your response! You're right and I didn't explain very well the situation.
Every time that a record are insert in the table, the flag New_Player be equal to 1 (which represents that is a new player). And stays equal to 1 until a new load be submited (and we found that there was a charge for DT_Execution).
Whenever there is A LOADING the flag New_Player passes to 0 (old player).
Imagine that a player was insert today (January)... during this month the flag New_Player of this Player are equal to 1, so I know that he's a new player. But, imagine that in 2018 I want see which were new customers in the month of January 2016. In 2018 the flag of this player in my database will be equal to 0, so I can't that he was a new player in that month.
I need to create a dynamic in my Data Warehouse to achieve that goal, right?
January 5, 2016 at 12:27 pm
antonio_scp125 (1/5/2016)
SSCertifiable thanks for your response! You're right and I didn't explain very well the situation.Every time that a record are insert in the table, the flag New_Player be equal to 1 (which represents that is a new player). And stays equal to 1 until a new load be submited (and we found that there was a charge for DT_Execution).
Whenever there is A LOADING the flag New_Player passes to 0 (old player).
Imagine that a player was insert today (January)... during this month the flag New_Player of this Player are equal to 1, so I know that he's a new player. But, imagine that in 2018 I want see which were new customers in the month of January 2016. In 2018 the flag of this player in my database will be equal to 0, so I can't that he was a new player in that month.
I need to create a dynamic in my Data Warehouse to achieve that goal, right?
I don't understand why you are relying so heavily on this New_Player flag, when the new player logic seems so straightforward.
Using the example you suggested, wouldn't the following query find those players who were new in Jan 2016?
select cols
from table
where DT_Contract >= '20160101' and DT_Contract < '20160201'
January 5, 2016 at 2:03 pm
I don't understand why you are relying so heavily on this New_Player flag, when the new player logic seems so straightforward.
Agreed. This violates that rule that fields depend on the primary key. In this case this field could be part of a query with this logic as a field calculated on the fly or part of a where clause if all you want to see our new records.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply