sql help: valid indicator for records that are >= 6 months

Database
Enthusiast

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

Tags (1)
3 REPLIES
Enthusiast

Re: sql help: valid indicator for records that are >= 6 months

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

Junior Contributor

Re: sql help: valid indicator for records that are >= 6 months

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

Enthusiast

Re: sql help: valid indicator for records that are >= 6 months

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.