Archives of the TeradataForum
Message Posted: Fri, 14 Dec 2012 @ 13:49:56 GMT
I think what you want is to keep the last four 'sets' of data, which may not be the last 4 calendar weeks of data. Correct ?
If so then you could do it with something like the following - assume column TXN_DT is the date column being used for deletion:
CREATE SET VOLATILE TABLE VT1 AS (SELECT TXN_DT,SUM(1) OVER(ORDER BY TXN_DT DESC ROWS UNBOUNDED PRECEDING) AS DATE_SEQ FROM (SELECT TXN_DT FROM T1 GROUP BY 1 ) AS DT1 QUALIFY DATE_SEQ = 4 ) WITH DATA ON COMMIT PRESERVE ROWS; DELETE FROM T1 WHERE T1.TXN_DT < VT1.TXN_DT;
I couldn't work out a way of getting the whole thing done in a single request because the SUM function can't be used in a sub-query. Probably someone else can.
Ward Analytics Ltd - Information in motion (www.ward-analytics.com)
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|