Recursive Date Query in Teradata

Just a quick blog to demonstrate how to write a recursive date query in Teradata. :)

First, you’ll need to create a “dummy view” in order to perform a UNION ALL:

CREATE VIEW DummyTable (DummyRow)
AS SELECT 1;

Then you’ll define the recursive statement and return the results:

/* Recursively define a range of dates; the specifics can easily be modified */
WITH RECURSIVE myDates(beginDate, endDate) AS 
(
    SELECT 
          DATE'2014-01-01' AS beginDate /* change to your start date */
        , DATE'2014-01-07' AS endDate /* change to the start date + desired interval */
    FROM 
        DummyTable
    UNION ALL
    SELECT 
          beginDate + INTERVAL '7' DAY /* change to the desired interval */
        , endDate + INTERVAL '7' DAY /* change to the desired interval */
    FROM 
        myDates
    WHERE 
        beginDate < CURRENT_DATE /* change to define the end of the date range */
) 
SELECT *
FROM myDates
ORDER BY 1;

Not too bad to write, eh? This can be used to do some pretty cool things, like condition change queries.

0saves
If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.
Bookmark the permalink.

One Response to Recursive Date Query in Teradata

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>