validate records that are >= 6 months apart

[FerozShaik]

I have a table that has customerid, and entrytimestamp. (combination of customerid and entrytimestamp is unique).

I am trying to mark some records in this table with valid_ind = 1. The first time a customer record is enterd in the table it can be marked as Valid.

A customer can be marked valid only after 6 months of being marked as eligible.

example:

customerid entrytimestamp valid_ind

1 2011-01-01 00:00:00 1 /* first record for a customer is automatically valid */

1 2011-02-01 00:00:00 0 /* last time this customer was marked valid is less than 6 months */

1 2011-05-29 00:00:00 0 /* last time this customer was marked valid is less than 6 months */

1 2011-06-15 00:00:00 1 /* last time this customer was marked valid is MORE than 6 months ago */

1 2011-07-15 00:00:00 0 /* last time this customer was marked valid is less than 6 months */

1 2011-12-13 00:00:00 0 /* last time this customer was marked valid is less than 6 months */

1 2011-12-25 00:00:00 1 /* last time this customer was marked valid is MORE than 6 months ago */

1 2012-06-30 00:00:00 1 /* last time this customer was marked valid is MORE than 6 months ago */



Please let me know if you have any questions.

i'd appreciate your help.

we are on Teradata 12.



Thanks.
Feroz

 

[FerozShaik]

This is the help that i got from Dieter.
works good.

CREATE VOLATILE TABLE vt AS
(
SELECT customerid,
entrytimestamp,
ROW_NUMBER() OVER (PARTITION BY customerid ORDER BY entrytimestamp) AS rn
FROM feroz
) WITH DATA PRIMARY INDEX (customerid, rn)
ON COMMIT PRESERVE ROWS
;


WITH RECURSIVE cte (customerid, entrytimestamp, prevTS, valid_ind, rn) AS
(
SELECT customerid, entrytimestamp, entrytimestamp, 1 AS valid_ind, 1 AS rn
WHERE rn = 1
FROM vt
UNION ALL
SELECT vt.customerid, vt.entrytimestamp,
CASE WHEN cte.prevTS < ADD_MONTHS(vt.entrytimestamp, -6) THEN vt.entrytimestamp ELSE cte.prevTS END,
CASE WHEN cte.prevTS < ADD_MONTHS(vt.entrytimestamp, -6) THEN 1 ELSE 0 END,
vt.rn
FROM vt JOIN cte
ON vt.customerid = cte.customerid AND vt.rn = cte.rn+1
)
SELECT * FROM cte
ORDER BY 1,2
;