INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

validate records that are >= 6 months apart

validate records that are >= 6 months apart

(OP)
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

RE: validate records that are >= 6 months apart

(OP)
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
;

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close