CODEWITHSUNDEEP
teradata
Buras
Buras

Reputation: 3099

How to use recursive with dates in Teradata?

I have the following table for people running a marathon

person  start       end
mike    2-Jun-14    2-Jul-14
nike    3-Jul-14    9-Aug-14
mini    1-Aug-14    3-Sep-14
sara    25-Jun-14   27-Jun-14
steve   12-Jun-14   3-Jul-14
stan    2-Jun-14    2-Aug-14
pete    3-Jul-14    9-Aug-14
tara    5-Jul-14    5-Sep-14

I need to create a table that shows if a person was in the process of running at the beginning of every month

The desired table should look like this 

person  running on
mike    1-Jul-14
nike    1-Aug-14
mini    1-Aug-14
mini    1-Sep-14
steve   1-Jul-14
stan    1-Jul-14
stan    1-Aug-14
pete    1-Aug-14
tara    1-Aug-14
tara    1-Sep-14

Right now, to do this I am running a separate query for each month. This is very tedious I suspect that i should be usingrecursive, but I do not know how to implement it here.

PS: I have Teradata 14 with all functions, except I cannot write my own udfs and procedures. How can I get the final table using Teradata sql ?

Upvotes: 2

Views: 979

Answers (1)

dnoeth
dnoeth

Reputation: 60462

There's no need for recursion or a procedure, this is done using some proprietary Teradata SQL extension:

SELECT person, BEGIN(pd)
FROM vt
EXPAND ON PERIOD(start, end) AS pd BY ANCHOR MONTH_BEGIN

Upvotes: 5

Related Questions