How To keep 3 days History in a Table ?

Database
Fan

How To keep 3 days History in a Table ?

Hi All,

I have a Date column say start_dt, based on this i have to keep only 3 days history in my table A?
I tried with Add_months like below
DELETE
From Table A
Where start_dt < ADD_MONTHS(start_dt, - 1)

but the above sql is keeping 1 month history...please suggest how i can limit this to 3 days only...

Thanks,
Deepti.Gaur
2 REPLIES
Teradata Employee

Re: How To keep 3 days History in a Table ?

Hello,

ADD_MONTHS definitely adds months, not days!

Following is the example, which may be of some help:

CREATE VOLATILE TABLE tab1 (col1 DATE, col2 DATE) ON COMMIT PRESERVE ROWS;

INSERT tab1(CAST('2008-01-01' AS DATE), CAST('2007-04-21' AS DATE));
INSERT tab1(CAST('2008-01-01' AS DATE), CAST('2007-08-01' AS DATE));
INSERT tab1(CAST('2008-01-01' AS DATE), CAST('2007-11-11' AS DATE));
INSERT tab1(CAST('2008-01-01' AS DATE), CAST('2007-11-01' AS DATE));

SELECT col1, col2, (col1 - col2) DAY(4) FROM tab1;

DROP TABLE tab1;

Regards,

Adeel
SN
Enthusiast

Re: How To keep 3 days History in a Table ?

hi Deepti,

if you add record for all keys everytime, then you can use this to delete the records older than 3 days after the load:

Del
from mytable where strt_dt <=
(sel max(strt_dt)-3 from mytable);

lets say you load table today, max(strt_dt) will be 2008-07-15 and you want to retain data only for 07/13,07/14 and 07/15.
The above delete stmt will do that.

or----- if the table can have different date for each record (say Col1 is the PK) then you can use this:

after the load run these:

create volatile table vt_max_strt_dt as (
sel Col1,max(strt_dt) as max_strt_dt from mytable group by 1) with data on commit preserve rows;

del
from mytable a,
vt_max_strt_dt vt
where a.col1 = vt.Col1 and strt_dt <= vt.max_strt_dt-3 ;

hope this helps!

thx