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.
Explain More
Pingback: Current Date In Sql Query | toptencreditcardcompanies.com