Archives of the TeradataForum

Message Posted: Fri, 14 Dec 2012 @ 13:49:56 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi John,

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.

Cheers,

Dave

Ward Analytics Ltd - Information in motion (www.ward-analytics.com)

	<Prev	Next>		<<First	<Prev	Next>	Last>>