Subscribe to Blog content and comments for RobG Latest blog posts

How do you determine the month-end date when building a date dimension?

One of our ETL developers recently pointed out that our date dimension (which I had populated) had incorrect values for month-end date, so I had to find a way to reliably calculate that value.

Goal: For every date in the calendar, determine the date of the last day of that month. Example: for every day in March of 2011, the month-end date is 2011-03-31.

Here is the logic I ended up using:

Problem: How do you add or subtract a "fractional second" to a TIMESTAMP column in Teradata?

The available documentation is not very clear, so let's look into it using examples.

Basic use of INTERVAL

Build, Populate, and Query Test Table

Problem: There are many cases where a database stores the previous value along with the replacement value. When an update happens, it is important to validate that (using CDC terms) the "before image" matches the most-recent "after image". If those don't match, then the chain of updates is broken.

The specific problem being solved here is where the prior value/new value pairs are stored in separate columns in the each row.

Key Prior Value New Value Date
1 A B 2011-02-05
1 B C 2011-02-06
1 C D 2011-02-07
1 D E 2011-02-08

These prior value/new value pairs generally build a chain. But how do you know when that chain is broken? We'll build an example and show the query that flags the records where the chain is broken.