Home Forums Data Warehousing Analysis Services Querying SCD Type 2 data to determine current value & date of change for a value that changes back and forth. RE: Querying SCD Type 2 data to determine current value & date of change for a value that changes back and forth.

  • March 11, 2015 at 12:09 pm

    #1783417

    touchupboy (2/25/2015)

    The subject kind of spells out what I am looking for. Attached is an example table image of an example of the issue.

    The ACTUAL answer I'm looking for is (transposed for text):

    CustomerID: 111

    CurrentSalesTerritory: 1

    CurrentTerritoryEffectiveDate: 8/1/2014

    CustomerID: 555

    CurrentSalesTerritory: 2

    CurrentTerritoryEffectiveDate: 7/2/2014

    CustomerID "111" is tricky because not only does the record version when ANY field changes (not just the one I am doing analysis on) but it sometimes goes back and forth.

    Sorry if this is a newbie question, but I'm new to SCD.

    This temporal issue is better solved in your ETL process. The addition of a SalesTerritoryEffectiveDate will help with the analysis you are trying to do. Without such a date, it won't be easy to extract what you are trying to do here.