All Forums Database
15 Oct 2012
sql help: valid indicator for records that are >= 6 months

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

Qaisar Kiani 337 posts Joined 11/05
16 Oct 2012

This is one way of solving it...
TEST_TBL is the one from which the customer records are selected, and MASTER_TBL contains the final results with indicators!

CREATE TABLE MASTER_TBL(
ID INTEGER,
INSERT_DT DATE,
VALID_IND INTEGER
) PRIMARY INDEX (ID)


CREATE TABLE TEMP_TBL(
ID INTEGER,
INSERT_DT DATE
) PRIMARY INDEX (ID)
 
 
INSERT INTO MASTER_TBL SELECT 1, CURRENT_DATE - INTERVAL '7' MONTH, 1;
INSERT INTO MASTER_TBL SELECT 1, CURRENT_DATE - INTERVAL '6' MONTH, 0;
INSERT INTO MASTER_TBL SELECT 2, CURRENT_DATE - INTERVAL '5' MONTH, 1;
INSERT INTO MASTER_TBL SELECT 3, CURRENT_DATE - INTERVAL '6' MONTH, 1;
INSERT INTO TEMP_TBL SELECT '1', CURRENT_DATE - INTERVAL '7' MONTH;
INSERT INTO TEMP_TBL SELECT '3', CURRENT_DATE - INTERVAL '6' MONTH;
INSERT INTO TEMP_TBL SELECT '2', CURRENT_DATE - INTERVAL '5' MONTH;


INSERT INTO MASTER_TBL
SEL A.ID, A.INSERT_DT, CASE WHEN A.INSERT_DT - INTERVAL '6' MONTH > B.INSERT_DT THEN 1 ELSE 0 END
FROM TEMP_TBL A
INNER JOIN
(
	SEL ID, INSERT_DT, VALID_IND
	FROM MASTER_TBL
	WHERE VALID_IND = 1
	QUALIFY ROW_NUMBER() OVER(PARTITION BY ID ORDER BY INSERT_DT DESC) = 1
) B
ON A.ID = B.ID

dnoeth 4628 posts Joined 11/04
16 Oct 2012

Hi Feroz,
you need some recursive process like this:

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
;

Btw, 2011-06-15 is less than 6 months from 2011-01-01 :-)
Dieter

Dieter

16 Oct 2012

Dieter,
Thank you very much for your help.
I tried the logic and it works great.
 
Quisar Aftab, thanks for looking into this i will work on that too and see how it works.
-Feroz.
 

You must sign in to leave a comment.