17.10 - Example: BETWEEN ... AND Query on ANSI System-Time Table - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - ANSI Temporal Table Support

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1186-171K
Language
English (United States)

To see the nature of the changes to the Fred and Alice rows, you need a query that will return the rows as they existed both before and after the change. You could submit a query with a BETWEEN ... AND temporal qualifier that spans the time of the change. We know from the AS OF query that the change occurred at 2005-05-01.

SELECT * 
FROM employee_systime 
FOR SYSTEM_TIME BETWEEN TIMESTAMP'2005-04-30 00:00:00.000001-08:00' AND                         TIMESTAMP'2005-05-02 00:00:00.000001-08:00' 
WHERE ename='Fred' OR ename='Alice' 
ORDER BY ename;

 eid ename  deptno                        sys_start                          sys_end
---- ------ ------ -------------------------------- --------------------------------
1005 Alice     222 2004-12-01 00:12:23.120000-08:00 2005-05-01 12:00:00.450000-08:00
1005 Alice     555 2005-05-01 12:00:00.450000-08:00 9999-12-31 23:59:59.999999+00:00
1004 Fred      222 2002-07-01 12:00:00.350000-08:00 2005-05-01 12:00:00.350000-08:00
1004 Fred      555 2005-05-01 12:00:00.350000-08:00 9999-12-31 23:59:59.999999+00:00

From the results it is clear that from December 1st, 2004 through May 1st, 2005 Alice was in Department 222. From May 1st, 2005 until now, Alice has been in Department 555. Similarly, Fred started in Department 222 and was there from July 1st, 2002 until May 1st, 2005, after which his department also changed to 555, and remains 555 today. Perhaps all the personnel in Department 222 switched departments in 2005, or possibly the department number was changed.