CODEWITHSUNDEEP
sqlloopsdata-warehouseteradata
user2008251
user2008251

Reputation: 23

recursively increment a date in sql

I am working on a historical conversion of data and was wondering if there's a more efficient way to accomplish a date increment.

I receive a data from a source system on a saturday date (1-7-13) and would like to push that data to make it fill all days of the previous week (1-6-13,1-5-13 ect).

So currently i am doing several unions

insert into target
(date, name)
select date,name 
from 
(
SELECT date as date, name FROM SOURCE
UNION
SELECT date - 1 as date, name FROM SOURCE
UNION
SELECT date -2 as date, name FROM SOURCE
)

I only ask because it looks like close to 500 million records are going to be going though this sql script. Incase it matters it is going to be running in a BTEQ script in TERADATA.

Upvotes: 0

Views: 4493

Answers (2)

sgeddes
sgeddes

Reputation: 62841

One option is to use a recursive query, but I don't think it would be much faster -- just perhaps easier to read:

WITH RECURSIVE recursiveCTE (date, name) AS (
  SELECT date, name
  FROM Source
  UNION ALL
  SELECT r.date-1, r.name
  FROM recursiveCTE R
     JOIN Source T ON R.name = T.name AND T.date < r.date+6
 )
INSERT INTO Target (date,name)
SELECT date,name From recursiveCTE

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269693

First, your code would be faster using union all rather than union. union removes duplicates, which does not seem to be needed in this case. If you do need them removed, then do it at the source level:

from (select distinct name from source)

Rather than doing it implicitly with union.

You can also try a cross join approach:

select date - i, name
from source cross join
     (select 0 as i union all select 1 union all select 2 union all select 3 union all
      select 4 union all select 5 union all select 6
     ) const

This might be a bit faster, because it doesn't need to set up the reads to the table multiple times.

Upvotes: 2

Related Questions