Sql-server – SQL SERVER 2014 – View value changes depending on the date

sql server

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.

Best Answer

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.

Related Solutions

Sql-server – Comparing DISTINCT, GROUP BY and ROW_NUMBER() in SQL Server 2008 with data warehouse workloads

In my experience, an aggregate (DISTINCT or GROUP BY) can be quicker then a ROW_NUMBER() approach. Saying that, ROW_NUMBER is better with SQL Server 2008 than SQL Server 2005.

However, you'll have to try for your situation.
Compare query plans, and use Profiler and SET to capture IO, CPU, Duration etc

For a lot of background, see these SO questions:

Why are logical reads for windowed aggregate functions so high? (follow the links from Martin Smith)
can I get count() and rows from one sql query in sql server? (note the comments in the answers from me and Chris Bednarski)

Finally, do you need the ROW_NUMBER approach? It looks like you're fixing a problem caused by de-normalisation.

And some notes:

shouldn't YearID be in the GROUP BY or PARTITION BY?
Won't DISTINCT give different output?
Are these columns indexed?

SQL Server – Moving a Partition of Partitioned Table to Another File Group

There are a couple of issues here. First, the partition/filegroup mapping shown in your question does not match the DDL. A RANGE RIGHT function boundary is inclusive of the partition on the right so the results should be as below to match the DDL. Consider downloading the partition details custom report from codeplex that can run from an SSMS object explorer table context menu to show the boundaries and filegroups: http://ssmspartcustomrpt.codeplex.com/releases/view/30936 .

OnlineGeoPosition   1   FG_OlderData    less than                  2015-01-21 00:00:00.000
OnlineGeoPosition   2   FG_OlderData    greater than or equal to   2015-01-21 00:00:00.000
OnlineGeoPosition   3   FG_OlderData    greater than or equal to   2015-02-20 00:00:00.000
OnlineGeoPosition   4   FG_OlderData    greater than or equal to   2015-03-21 00:00:00.000
OnlineGeoPosition   5   FG_NewerData    greater than or equal to   2015-04-21 00:00:00.000
OnlineGeoPosition   6   FG_NewerData    greater than or equal to   2015-05-22 00:00:00.000
OnlineGeoPosition   7   FG_NewerData    greater than or equal to   2015-06-22 00:00:00.000
OnlineGeoPosition   8   FG_NewerData    greater than or equal to   2015-07-23 00:00:00.000

When you merge a RANGE RIGHT function, the removed partition is the one on the right. Data from the removed partition are moved to the retained one on the left. So merging a RANGE RIGHT sliding window partition always moves from newer to older partitions. However it is best to MERGE and SPLIT empty partitions as you are doing to avoid expensive data movement during partition DDL, which is about 4 times that of normal DML. Conversely, a SPLIT of a RANGE RIGHT function creates a new partition to the right of the specified boundary, and on the NEXT used filegroup, which is the main problem you are having.

Below is a sample script to achieve a sliding window with the new partition/filegroup mappings below. This script uses 2 staging tables, one for the latest partition on the old data filegroup and another for the oldest partition on the new data filegroup (the one to be moved). This allows the boundary to be safely merged with both adjacent partitions empty. You'll need either check constraints on the staging tables or another partition function/scheme to constrain the staging table data to match the target partition boundaries for the switch in. Data are moved efficiently using a CREATE INDEX...DROP_EXISTING.

OnlineGeoPosition   1   FG_OlderData    less than   2015-02-20 00:00:00.000
OnlineGeoPosition   2   FG_OlderData    greater than or equal to   2015-02-20 00:00:00.000
OnlineGeoPosition   3   FG_OlderData    greater than or equal to   2015-03-21 00:00:00.000
OnlineGeoPosition   4   FG_OlderData    greater than or equal to   2015-04-21 00:00:00.000
OnlineGeoPosition   5   FG_NewerData    greater than or equal to   2015-05-22 00:00:00.000
OnlineGeoPosition   6   FG_NewerData    greater than or equal to   2015-06-22 00:00:00.000
OnlineGeoPosition   7   FG_NewerData    greater than or equal to   2015-07-23 00:00:00.000
OnlineGeoPosition   8   FG_NewerData    greater than or equal to   2015-08-22 00:00:00.000

Sample script:

--switch out newest partition on FG_OldData
ALTER TABLE dbo.OnlineGeoPosition
SWITCH PARTITION $PARTITION.pf_Positions_LastSixMonth_MonthlyRange('2015-03-21')
TO dbo.OnlineGeoPosition_OlderTemp;

--switch out oldest partition on FG_NewData
ALTER TABLE dbo.OnlineGeoPosition
SWITCH PARTITION $PARTITION.pf_Positions_LastSixMonth_MonthlyRange('2015-04-21')
TO dbo.OnlineGeoPosition_NewerToOlderTemp;

--move empty partition from new data to old data
ALTER PARTITION FUNCTION pf_Positions_LastSixMonth_MonthlyRange()
    MERGE RANGE ('2015-04-21');

--create new empty partition on old data filegroup for moved data
ALTER PARTITION SCHEME ps_Positions_LastSixMonth_MontlyRange
          NEXT USED FG_OlderData;
ALTER PARTITION FUNCTION pf_Positions_LastSixMonth_MonthlyRange()
    SPLIT RANGE ('2015-04-21');

--rebuild each index with drop existing to move data from new to old filegroup
CREATE UNIQUE CLUSTERED INDEX PK_OnlineGeoPosition_NewerToOlderTemp
ON dbo.OnlineGeoPosition_NewerToOlderTemp(PartitioningColumn)
WITH(DROP_EXISTING=ON)
ON FG_OlderData;

--switch staging tables data back in
--staging tables must have a check constraint or be partitoned similarly as the target partition
ALTER TABLE dbo.OnlineGeoPosition_OlderTemp
SWITCH TO dbo.OnlineGeoPosition PARTITION $PARTITION.pf_Positions_LastSixMonth_MonthlyRange('2015-03-21')
ALTER TABLE dbo.OnlineGeoPosition_NewerToOlderTemp
SWITCH TO dbo.OnlineGeoPosition PARTITION $PARTITION.pf_Positions_LastSixMonth_MonthlyRange('2015-04-21')

--remove oldest partition
TRUNCATE TABLE dbo.OnlineGeoPosition_OlderTemp;
ALTER TABLE dbo.OnlineGeoPosition
SWITCH PARTITION $PARTITION.pf_Positions_LastSixMonth_MonthlyRange('2015-01-21')
TO dbo.OnlineGeoPosition_OlderTemp;
TRUNCATE TABLE dbo.OnlineGeoPosition_OlderTemp;
ALTER PARTITION FUNCTION pf_Positions_LastSixMonth_MonthlyRange()
    MERGE RANGE ('2015-01-21');

--create new partition on new data filegroup for future data
ALTER PARTITION SCHEME ps_Positions_LastSixMonth_MontlyRange
          NEXT USED FG_NewerData;
ALTER PARTITION FUNCTION pf_Positions_LastSixMonth_MonthlyRange()
    SPLIT RANGE ('2015-08-22');
GO

Best Answer

Related Solutions

Sql-server – Comparing DISTINCT, GROUP BY and ROW_NUMBER() in SQL Server 2008 with data warehouse workloads

SQL Server – Moving a Partition of Partitioned Table to Another File Group

Related Question