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.

2 Responses to Recursive Date Query in Teradata

  1. Pingback: Current Date In Sql Query | toptencreditcardcompanies.com

Leave a Reply

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