Adding a calculated column for an IsCurrent record SCD

You want to add a simple calculated column to identify a current record using some criteria.  An example might be the Slow Changing Dimension task in SSIS, which only provides a null end date for new records inserted into the table.

The following example adds a calculated column called “IsCurrent” to the table “YourDimensionTable” based on records that have a null end date for the column “date_to”.


ALTER TABLE YourDimensionTable

ADD IsCurrent AS CASE WHEN date_to is null then 'Yes' else 'No' end

GO

Now you have provided  a more intuitive method for identifying or slicing current records i.e.


SELECT * FROM YourDimensionTable WHERE IsCurrent = 'Yes'

On a side, there is a reference here for customising the scd task to cater for custom dates…

http://www.bp-msbi.com/2010/04/custom-dates-for-an-ssis-scd-task/

 

About AussieBICG

Connect with me here https://au.linkedin.com/in/aussiebicg

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s