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

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

2) FromDate should not be earlier than ToDate

(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


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: 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