How to create a view (with end-date) for an INSERT-ONLY SCD Type 2 table?

Well Albert Garcia Diaz wrote a great example for that.
He uses LEAD() combined with the OVER-clause to easily accomplish this:
“…LEAD provides access to a row at a given physical offset that follows the current row…”

More info about LEAD():
https://msdn.microsoft.com/en-us/library/hh213125.aspx


CREATE VIEW [hstg].[vw_Table_History]
AS
SELECT
ETL_EXECUTION_DT AS START_DATE,
ISNULL(LEAD(ETL_EXECUTION_DT) OVER (PARTITION BY BusinessKey ORDER BY ETL_EXECUTION_DT), '9999-12-31 00:00:00.0000000') AS END_DATE,
[CURRENT_RECORD] = CASE
WHEN LEAD(ETL_EXECUTION_DT) OVER (PARTITION BY BusinessKey ORDER BY ETL_EXECUTION_DT) IS NULL THEN 'Y'
ELSE 'N'
END,

[BusinessKey],
[Field1],
[Field2],

ETL_BATCH_DT AS ETL_ROW_START_DT,
S.ETL_BATCH_ROW_ID,
CONVERT (DATETIME, '99991231') AS ETL_ROW_END_DT,
ETL_ROW_HASH,
CONVERT (NVARCHAR (512), 'AdvWorks') AS ETL_ROW_SOURCE,
ETL_CDC_OPERATION,
ETL_EXECUTION_DT
FROM [hstg].[Table] S








Advertisements

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