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


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…


Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s