Example: Current Modifications Do Not Apply to Future Rows - Analytics Database - Teradata Vantage

Temporal Table Support

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
ft:locale
en-US
ft:lastEdition
2023-10-30
dita:mapPath
eud1628112402879.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
fif1472244754791
lifecycle
latest
Product Category
Teradata Vantage™

This example demonstrates that current data modifications do not apply to future rows. Assume the following tables describe a company’s employees and departments:

CREATE MULTISET TABLE employee ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      eid INTEGER NOT NULL,
      ename VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      bdate DATE FORMAT 'yyyy/mm/dd',
      job_duration PERIOD(DATE) NOT NULL AS VALIDTIME,
      deptid INTEGER,
      mid INTEGER)

PRIMARY INDEX ( eid );

CREATE MULTISET TABLE dept ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      deptid INTEGER NOT NULL,
      deptname VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC)
UNIQUE PRIMARY INDEX ( deptid );

Assume the company management must cut down the staff of the SUPPORT department. Employees who have been with the company for less than three months must be discharged. The following query removes employees with job durations of less than three months:

CURRENT VALIDTIME
DELETE employee
FROM dept
WHERE dept.deptname = 'SUPPORT' AND
      dept.deptid = employee.deptid AND
      BEGIN(job_duration) > CURRENT_DATE - interval '3' month;

Now assume that the system includes employee entries for new employees who have not yet started working at the company. The preceding DELETE statement will not remove these future employees. To remove them together with the current employees, the following statement could be used:

BT;
/* delete currently employees in department with less than 3 months
work*/
CURRENT VALIDTIME
DELETE employee
FROM dept
WHERE dept.deptname = 'SUPPORT' AND
      dept.deptid = employee.deptid AND
      BEGIN(job_duration) > current_date - interval '3' month;

/* delete all future employees */
SEQUENCED VALIDTIME
DELETE employee
FROM dept
WHERE dept.deptname = 'SUPPORT' AND
      dept.deptid = employee.deptid AND
      BEGIN(job_duration) > TEMPORAL_DATE;

ET;

Alternatively, the following SQL would accomplish the same:

REPLACE VIEW v1 AS
NONSEQUENCED VALIDTIME
SELECT employee.eid, dept.deptid
FROM employee, dept
WHERE dept.deptname = 'SUPPORT' AND
 dept.deptid = employee.deptid AND
BEGIN(job_duration) > CURRENT_DATE - interval '3' month
AND job_duration OVERLAPS PERIOD(TEMPORAL_DATE, UNTIL_CHANGED);
SEQUENCED VALIDTIME PERIOD(TEMPORAL_DATE, UNTIL_CHANGED)
DELETE employee FROM v1 WHERE v1.eid = employee.eid AND 
 v1.deptid = employee.deptid;