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

Teradata Vantage™ - Temporal Table Support

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1182-171K
Language
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;
ID VALIDTIME
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.