How to check for Contiguous Dates


Let’s say we have an SCD 2 Dimension called Dimension.Store.  Lets assume the Business Key is BranchCode and the type II rows is versioned using RowStartDate & RowEndDate  columns.

How do we verify if the dates are contiguous with no gaps between different Type II Versions?

1) FromDate is not duplicated within the same Business Key

IF EXISTS
(SELECT BranchCode, [RowStartDate], COUNT(*)
FROM Dimension.Store
GROUP BY BranchCode, [RowStartDate]
HAVING COUNT(*) > 1)
SELECT ‘Error::Duplicate From Date Found for the same Branch’

2) FromDate should not be earlier than ToDate

IF EXISTS
(SELECT 1 FROM Dimension.Store WHERE RowStartDate > RowEndDate
)
SELECT ‘Error::To Date is Earlier Than From Date’

3) There should be no gaps Between EndDate of the previous Row and StartDate of the next row

SELECT BranchCode,
CASE WHEN DATEDIFF(DAY, MIN(RowStartDate),MAX(RowEndDate)) + 1 <> SUM(DATEDIFF(D, RowStartDate,RowEndDate) + 1) THEN ‘Bad::GapsInDateColumns’ ELSE ‘Good::NoGapsFoundInDateColumns’ END AS ErrorStatus
FROM Dimension.Store
GROUP BY BranchCode

Advertisements

About EasyBIJegan

Business Intelligence Developer
This entry was posted in Data Quality, DataWareHousing, Uncategorized. Bookmark the permalink.

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