17.10 - Example: Using a sequenced aggregation with GROUP BY - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Temporal Table Support

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
Publication ID
English (United States)

Sequenced aggregations together with GROUP BY in the query includes "empty" valid-time periods during which none of the temporal table rows is valid. Assume the temporal table from the example above included another row for which the valid-time period was not contiguous with any of the existing valid time periods.

ID Job_Type ChargePerDay Duration
123 Cockpit 40 1 Jan 2012 – 1 Mar 2012

The query above would have returned two additional rows, one for the new duration, and one for the period that included no aircraft maintenance work:

ID TotalChargePerDay AvgChargePerDay VALIDTIME
123 ? (NULL) ? (NULL) ('11/01/09', '12/01/01')
123 40 40 ('12/01/01', '12/03/01')

This allows you to answer questions such as "During the time covered by the table, when were no aircraft undergoing service?"

SEQUENCED VALIDTIME PERIOD(date'2011-01-01', date'2012-03-01')
SELECT id FROM aircraft_service
       HAVING COUNT(ChargePerDay)= 0
       GROUP BY 1
       ORDER BY 1;
123 ('11/01/09', '12/01/01')

To prevent the "empty" valid-time period from being returned, add a condition to the query using the HAVING clause to filter out these rows.