At times we need to join records in one table with a specified range of dates in a second table. One application which comes to my mind in the first place is to fake a snapshot history table (containing a snapshot date) based on a change history table (containing timelines limited by a start date and an end date). Here is one example:
CREATE TABLE Snapshot_History
PK INTEGER NOT NULL,
CREATE TABLE Change_History
PK INTEGER NOT NULL,
The solution would probably look like this:
SELECT t01.PK, t01.ATTRIB, t02, table?CALENDAR_DATE AS SNAPSHOT_DATE
t02.CALENDAR_DATE BETWEEN START_DATE AND END_DATE;
But what should you do if you do not have permissions to read from the SYS_CALENDAR.CAtable? You probably will have to create your calendar table, if you are not in the lucky situation that somewhere in your Teradata Data Warehouse exists already one as standard.
I came to hate building my calendar tables for several reasons, but principal reason is that each Data Warehouse I have seen until now ended up having calendar tables widely scattered across all subject areas. To make things even worse, this tables will show up in different shapes and naming conventions, making your life even harder.
Hence I started to research for a solution which should not need any real table at all. Further, from a performance point of view, I wanted to avoid the joining of tens of thousands of calendar dates despite only needing a few dates.
From my experience, you can end up with an awful performing query if Teradata is not able to apply early in the execution plan some partition elimination on the unneeded dates (This is especially valid for any Teradata Release before 13).
This was the moment when I started to play around with recursions and here is the result (you may probably want to transform the below SQL statement into a recursive view). My solution does not need any real table, and it only creates the range of dates you want:
WITH RECURSIVE DateRanges(CALENDAR_DATE) AS
SELECT DATE’2014-04-01′ AS CALENDAR_DATE
( SELECT * FROM (SELECT NULL AS X) X) DUMMYTABLE
CALENDAR_DATE + INTERVAL ‘1’ DAY
CALENDAR_DATE < CURRENT_DATE
I am sure; you will find many other useful applications by adapting above SQL statement. I could, for example, imagine to change it in a way to deliver only the last day of each month for a certain time range.
I leave it to you as an easy task to post any further solutions.
Teradata recursions can be helpful if your requirement is to create any ranks. Besides, the “dummy table creation” statement “SELECT * FROM (SELECT NULL AS X) X” permits to achieve this without any physical tables.