DELTA_T Function Examples | Teradata Vantage - Examples - Advanced SQL Engine

Example: Creating the OCEAN_BUOYS_DELTA_T Table

/*PTI Table*/
CREATE TABLE OCEAN_BUOYS_DELTA_T(BUOYID INTEGER, SALINITY INTEGER, TEMPERATURE INTEGER ) 
PRIMARY TIME INDEX (TIMESTAMP(6), DATE '2012-01-01', HOURS(1), COLUMNS(BUOYID), nonsequenced);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 08:00:00.000000', 0, 55, 10);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 08:08:59.999999', 0, 55, );
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 08:09:59.999999', 0, 55, 99);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 08:10:00.000000', 0, 55, 10);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 08:10:01.000000', 0, 55, 100);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 09:01:25.122200', 1, 55, 77);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 09:01:25.122200', 1, 55, 70);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 09:02:25.122200', 1, 55, 78);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 09:02:25.122200', 1, 55, 71);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 09:03:25.122200', 1, 55, 79);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 09:03:25.122200', 1, 55, 72);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 21:01:25.122200', 2, 55, 80);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 21:02:25.122200', 2, 55, 81);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 21:03:25.122200', 2, 55, 82);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:00:24.000000', 44, 55, 43);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:00:24.333300', 44, 55, 43);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:00:25.122200', 44, 55, 43);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:00:26.122200', 44, 55, 43);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:01:25.122200', 44, 55, 53);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:01:25.122200', 44, 55, 54);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:02:25.122200', 44, 55, 53);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:02:25.122200', 44, 55, 55);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:03:25.122200', 44, 55, 53);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:03:25.122200', 44, 55, 56);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:12:00.000000', 44, 55, 43);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:32:12.122200', 44, 55, 43);
INSERT INTO OCEAN_BUOYS_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:52:00.000009', 44, 55, 43);

/*Non-PTI Table*/
CREATE TABLE OCEAN_BUOYS_NONPTI_DELTA_T(TIMECODE TIMESTAMP(6), BUOYID INTEGER, SALINITY INTEGER, TEMPERATURE INTEGER)
PRIMARY INDEX (BUOYID);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 08:00:00.000000', 0, 55, 10);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 08:08:59.999999', 0, 55, );
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 08:09:59.999999', 0, 55, 99);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 08:10:00.000000', 0, 55, 10);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 08:10:01.000000', 0, 55, 100);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 09:01:25.122200', 1, 55, 77);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 09:01:25.122200', 1, 55, 70);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 09:02:25.122200', 1, 55, 78);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 09:02:25.122200', 1, 55, 71);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 09:03:25.122200', 1, 55, 79);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 09:03:25.122200', 1, 55, 72);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 21:01:25.122200', 2, 55, 80);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 21:02:25.122200', 2, 55, 81);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 21:03:25.122200', 2, 55, 82);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:00:24.000000', 44, 55, 43);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:00:24.333300', 44, 55, 43);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:00:25.122200', 44, 55, 43);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:00:26.122200', 44, 55, 43);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:01:25.122200', 44, 55, 53);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:01:25.122200', 44, 55, 54);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:02:25.122200', 44, 55, 53);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:02:25.122200', 44, 55, 55);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:03:25.122200', 44, 55, 53);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:03:25.122200', 44, 55, 56);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:12:00.000000', 44, 55, 43);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:32:12.122200', 44, 55, 43);
INSERT INTO OCEAN_BUOYS_NONPTI_DELTA_T VALUES(TIMESTAMP '2014-01-06 10:52:00.000009', 44, 55, 43);

Example: Searching the Minimum and Maximum Observed Temperatures

This example measures the time between minimum and maximum observed temperatures every 10 minutes between 8:00 AM and 10:30 AM on each buoy.

/*PTI Table*/
SELECT $TD_TIMECODE_RANGE, $TD_GROUP_BY_TIME, t1.BUOYID, DELTA_T((WHERE TEMPERATURE=MIN_TEMP),(WHERE TEMPERATURE=MAX_TEMP))
FROM (
      SELECT MIN(TEMPERATURE) AS MIN_TEMP, MAX(TEMPERATURE) AS MAX_TEMP, BUOYID
      FROM OCEAN_BUOYS_DELTA_T
      WHERE TD_TIMECODE BETWEEN TIMESTAMP '2014-01-06 08:00:00' AND TIMESTAMP '2014-01-06 10:30:00'
      GROUP BY TIME(MINUTES(30) AND BUOYID)
) AS t1, OCEAN_BUOYS_DELTA_T
WHERE t1.buoyid=OCEAN_BUOYS_DELTA_T.buoyid
GROUP BY TIME(DAYS(1) and t1.buoyid)
USING TIMECODE(OCEAN_BUOYS_DELTA_T.td_timecode)
order by 2, 3;

/*Non-PTI Table*/
SELECT $TD_TIMECODE_RANGE, $TD_GROUP_BY_TIME, t1.BUOYID, DELTA_T((WHERE TEMPERATURE=MIN_TEMP),(WHERE TEMPERATURE=MAX_TEMP))
FROM (
      SELECT MIN(TEMPERATURE) AS MIN_TEMP, MAX(TEMPERATURE) AS MAX_TEMP, BUOYID
      FROM OCEAN_BUOYS_NONPTI_DELTA_T
      WHERE TIMECODE BETWEEN TIMESTAMP '2014-01-06 08:00:00' AND TIMESTAMP '2014-01-06 10:30:00'
      GROUP BY TIME(MINUTES(30) AND BUOYID)
      USING TIMECODE(TIMECODE)
) AS t1, OCEAN_BUOYS_NONPTI_DELTA_T
WHERE t1.buoyid=OCEAN_BUOYS_NONPTI_DELTA_T.buoyid
GROUP BY TIME(DAYS(1) and t1.buoyid)
USING TIMECODE(OCEAN_BUOYS_NONPTI_DELTA_T.timecode)
order by 2, 3;

The results for both tables:

TIMECODE_RANGE	GROUP BY TIME (MINUTES(10))	BUOYID	Delta_T(TD_ TIMECODE)
('2014-01-06 00:00:00.000000+00:00', '2014-01-07 00:00:00.000000+00:00')	737	0	('2014-01-06 08:10:00.000000', '2014-01-06 08:10:01.000000')
('2014-01-06 00:00:00.000000+00:00', '2014-01-07 00:00:00.000000+00:00')	737	1	('2014-01-06 09:01:25.122200', '2014-01-06 09:03:25.122200')
('2014-01-06 00:00:00.000000+00:00', '2014-01-07 00:00:00.000000+00:00')	737	44	('2014-01-06 10:00:26.122200', '2014-01-06 10:03:25.122200')

Buoy 44, timebucket 13, results are based on the final measurement meeting the start condition. No result row returned for buoy 44, timebucket 14, because no entries met the start and end conditions.

Example: Creating a Multiset Table to Track Elapsed Time

In this example, create a table with information about parcels sent by a delivery service.

/*PTI Table*/
CREATE MULTISET TABLE package_tracking_pti(ParcelNumber INTEGER, Status VARCHAR(512)) 
PRIMARY TIME INDEX (TIMESTAMP(6), DATE '2012-01-01', HOURS(1), COLUMNS(ParcelNumber), nonsequenced);
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-15 08:00:00', 55, 
'picked up from customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-15 08:05:00', 59, 
'picked up from customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-15 09:10:00', 55, 
'arrived at receiving station');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-15 09:20:00', 60, 
'picked up from customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-15 10:00:00', 55, 
'in transit to destination');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-15 10:45:00', 60, 
'arrived at receiving station');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-15 16:10:00', 55, 
'arrived at destination station');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-15 16:30:00', 55, 
'in transit to customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-15 17:00:00', 55, 
'delivered to customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-15 08:00:00', 75, 
'picked up from customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-15 08:05:00', 79, 
'picked up from customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-15 09:10:00', 75, 
'arrived at receiving station');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-15 09:20:00', 80, 
'picked up from customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-15 10:00:00', 75, 
'in transit to destination');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-15 10:45:00', 80, 
'arrived at receiving station');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-15 16:10:00', 75, 
'arrived at destination station');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-15 16:30:00', 75, 
'in transit to customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-15 17:00:00', 75, 
'delivered to customer');

/*Non-PTI Table*/
CREATE MULTISET TABLE package_tracking(ParcelNumber INTEGER, CLOCK_TIME TIMESTAMP, Status VARCHAR(512));
INSERT INTO package_tracking(55, TIMESTAMP'2016-10-15 08:00:00', 
'picked up from customer');
INSERT INTO package_tracking(59, TIMESTAMP'2016-10-15 08:05:00', 
'picked up from customer');
INSERT INTO package_tracking(55, TIMESTAMP'2016-10-15 09:10:00', 
'arrived at receiving station');
INSERT INTO package_tracking(60, TIMESTAMP'2016-10-15 09:20:00', 
'picked up from customer');
INSERT INTO package_tracking(55, TIMESTAMP'2016-10-15 10:00:00', 
'in transit to destination');
INSERT INTO package_tracking(60, TIMESTAMP'2016-10-15 10:45:00', 
'arrived at receiving station');
INSERT INTO package_tracking(55, TIMESTAMP'2016-10-15 16:10:00', 
'arrived at destination station');
INSERT INTO package_tracking(55, TIMESTAMP'2016-10-15 16:30:00', 
'in transit to customer');
INSERT INTO package_tracking(55, TIMESTAMP'2016-10-15 17:00:00', 
'delivered to customer');
INSERT INTO package_tracking(75, TIMESTAMP'2016-10-15 08:00:00', 
'picked up from customer');
INSERT INTO package_tracking(79, TIMESTAMP'2016-10-15 08:05:00', 
'picked up from customer');
INSERT INTO package_tracking(75, TIMESTAMP'2016-10-15 09:10:00', 
'arrived at receiving station');
INSERT INTO package_tracking(80, TIMESTAMP'2016-10-15 09:20:00', 
'picked up from customer');
INSERT INTO package_tracking(75, TIMESTAMP'2016-10-15 10:00:00', 
'in transit to destination');
INSERT INTO package_tracking(80, TIMESTAMP'2016-10-15 10:45:00', 
'arrived at receiving station');
INSERT INTO package_tracking(75, TIMESTAMP'2016-10-15 16:10:00', 
'arrived at destination station');
INSERT INTO package_tracking(75, TIMESTAMP'2016-10-15 16:30:00', 
'in transit to customer');
INSERT INTO package_tracking(75, TIMESTAMP'2016-10-15 17:00:00', 
'delivered to customer');

Example: Finding Time Elapsed between Shipping and Receiving an Item

/*PTI Table*/
SELECT $TD_TIMECODE_RANGE, ParcelNumber, DELTA_T(
             (WHERE Status LIKE 'picked%up%customer'),  
             (WHERE Status LIKE 'delivered%customer'))
FROM package_tracking_pti
GROUP BY TIME(* AND ParcelNumber);

/*Non-PTI Table*/
SELECT $TD_TIMECODE_RANGE, ParcelNumber, DELTA_T(
             (WHERE Status LIKE 'picked%up%customer'),  
             (WHERE Status LIKE 'delivered%customer'))
FROM package_tracking
GROUP BY TIME(* AND ParcelNumber) USING TIMECODE(CLOCK_TIME);

The results are the same for both tables:

TIMECODE_RANGE	ParcelNumber	Delta_T(TD_ TIMECODE)
('2016-10-16 08:00:00.000000+00:00', '2016-10-17 17:00:00.000000+00:00')	55	('2016-10-15 08:00:00.000000', '2016-10-15 17:00:00.000000')
('2016-10-16 08:00:00.000000+00:00', '2016-10-17 17:00:00.000000+00:00')	75	('2016-10-15 08:00:00.000000', '2016-10-15 17:00:00.000000')

Example: Tracking an Incorrectly Delivered Package

This example is for an incorrectly delivered package. The package, picked up and delivered twice, resulted in two output rows.

DELETE package_tracking_pti;
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-16 08:00:00', 65, 
'picked up from customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-16 08:05:00', 69, 
'picked up from customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-16 09:10:00', 65, 
'arrived at receiving station');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-16 09:20:00', 70, 
'picked up from customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-16 10:00:00', 65, 
'in transit to destination');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-16 10:45:00', 70, 
'arrived at receiving station');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-16 16:10:00', 65, 
'arrived at destination station');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-16 16:30:00', 65, 
'in transit to customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-16 17:00:00', 65, 
'delivered to customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-16 17:05:00', 65, 
'notification of incorrect delivery');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-17 08:05:00', 65, 
'picked up from customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-17 09:10:00', 65, 
'arrived at receiving station');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-17 10:00:00', 65, 
'in transit to destination');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-17 16:30:00', 65, 
'in transit to customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-17 17:00:00', 65, 
'delivered to customer');


DELETE package_tracking;
INSERT INTO package_tracking(65, TIMESTAMP'2016-10-16 08:00:00', 
'picked up from customer');
INSERT INTO package_tracking(69, TIMESTAMP'2016-10-16 08:05:00', 
'picked up from customer');
INSERT INTO package_tracking(65, TIMESTAMP'2016-10-16 09:10:00', 
'arrived at receiving station');
INSERT INTO package_tracking(70, TIMESTAMP'2016-10-16 09:20:00', 
'picked up from customer');
INSERT INTO package_tracking(65, TIMESTAMP'2016-10-16 10:00:00', 
'in transit to destination');
INSERT INTO package_tracking(70, TIMESTAMP'2016-10-16 10:45:00', 
'arrived at receiving station');
INSERT INTO package_tracking(65, TIMESTAMP'2016-10-16 16:10:00', 
'arrived at destination station');
INSERT INTO package_tracking(65, TIMESTAMP'2016-10-16 16:30:00', 
'in transit to customer');
INSERT INTO package_tracking(65, TIMESTAMP'2016-10-16 17:00:00', 
'delivered to customer');
INSERT INTO package_tracking(65, TIMESTAMP'2016-10-16 17:05:00', 
'notification of incorrect delivery');
INSERT INTO package_tracking(65, TIMESTAMP'2016-10-17 08:05:00', 
'picked up from customer');
INSERT INTO package_tracking(65, TIMESTAMP'2016-10-17 09:10:00', 
'arrived at receiving station');
INSERT INTO package_tracking(65, TIMESTAMP'2016-10-17 10:00:00', 
'in transit to destination');
INSERT INTO package_tracking(65, TIMESTAMP'2016-10-17 16:30:00', 
'in transit to customer');
INSERT INTO package_tracking(65, TIMESTAMP'2016-10-17 17:00:00', 
'delivered to customer');

To find the results for the tables:

/*PTI Table*/
SELECT $TD_TIMECODE_RANGE, ParcelNumber, DELTA_T(
              (WHERE Status LIKE 'picked%up%customer'),  
              (WHERE Status LIKE 'delivered%customer'))
FROM package_tracking_pti
GROUP BY TIME(* AND ParcelNumber);

/*Non-PTI Table*/
SELECT $TD_TIMECODE_RANGE, ParcelNumber, DELTA_T(
              (WHERE Status LIKE 'picked%up%customer'),  
              (WHERE Status LIKE 'delivered%customer'))
FROM package_tracking
GROUP BY TIME(* AND ParcelNumber) USING TIMECODE(CLOCK_TIME);

The results are the same for both tables:

TIMECODE_RANGE	ParcelNumber	DELTA_T(TD_ TIMECODE)
('2016-10-16 08:00:00.000000+00:00', '2016-10-17 17:00:00.000000+00:00')	65	('2016-10-16 08:00:00.000000', '2016-10-16 17:00:00.000000')
('2016-10-16 08:00:00.000000+00:00', '2016-10-17 17:00:00.000000+00:00')	65	('2016-10-17 08:05:00.000000', '2016-10-17 17:00:00.000000')

Example: Tracking Shipping Days and Percentage of Delivery Failures

In this example, the marketing team starts a campaign to guarantee package delivery within a specified number of days. If the package is not delivered within that date range, the company pays for shipping.

To minimize shipping charges, the company analyzes recent deliveries, determining how many packages missed the delivery dates. The analysis uses DELTA_T with HAVING clause filtration.

/*PTI Table*/
DELETE package_tracking_pti;
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-16 08:00:00', 65, 'picked up from customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-16 08:05:00', 69, 'picked up from customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-16 09:10:00', 65, 'arrived at receiving station');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-16 09:20:00', 70, 'picked up from customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-16 10:00:00', 65, 'in transit to destination');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-16 10:45:00', 70, 'arrived at receiving station');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-16 16:10:00', 65, 'arrived at destination station');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-16 16:30:00', 65, 'in transit to customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-16 17:00:00', 65, 'delivered to customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-17 10:00:00', 70, 'in transit to customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-17 10:45:00', 69, 'arrived at receiving station');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-17 11:45:00', 69, 'in transit to customer'); 
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-17 14:00:00', 69, 'delivered to customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-17 15:00:00', 70, 'delivered to customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-17 08:00:00', 71, 'picked up from customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-18 09:10:00', 71, 'arrived at receiving station');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-20 16:30:00', 71, 'in transit to customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-20 17:00:00', 71, 'delivered to customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-21 08:00:00', 72, 'picked up from customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-25 09:10:00', 72, 'arrived at receiving station');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-27 16:30:00', 72, 'in transit to customer');
INSERT INTO package_tracking_pti(TIMESTAMP'2016-10-27 17:00:00', 72, 'delivered to customer');

To find how many packages took more than two days to deliver, use this example:

/*PTI Table*/
SELECT COUNT(*) AS FailedDeliveries FROM
(
    SELECT DELTA_T((WHERE Status LIKE 'picked%up%customer'),
                   (WHERE Status LIKE 'delivered%customer')) AS deltaT
    FROM package_tracking_pti
    GROUP BY TIME(* AND ParcelNumber)
    HAVING INTERVAL(deltaT) DAY TO SECOND > INTERVAL '2 00:00:00' DAY TO SECOND
) as t1;

Result:

FailedDeliveries
2

To find how many packages took more than five days to deliver, use this example:

/*PTI Table*/
SELECT COUNT(*) AS FailedDeliveries FROM
(
    SELECT DELTA_T((WHERE Status LIKE 'picked%up%customer'),
                   (WHERE Status LIKE 'delivered%customer')) AS deltaT
    FROM package_tracking_pti
    GROUP BY TIME(* AND ParcelNumber)
    HAVING INTERVAL(deltaT) DAY TO SECOND > INTERVAL '5 00:00:00' DAY TO SECOND
) as t1;

Result:

FailedDeliveries
1

Find the percentage of packages taking longer than five days to deliver:

/*PTI Table*/
SELECT CAST(SUM(CASE
                WHEN INTERVAL(deltaT) DAY TO SECOND > INTERVAL '5 00:00:00' DAY TO SECOND
                THEN 1
                ELSE 0
                END) AS REAL) /
       CAST(COUNT(*) AS REAL) * 100 (FORMAT 'zz.zz%') AS "%FailedDeliveries"
FROM
(
    SELECT DELTA_T((WHERE Status LIKE 'picked%up%customer'),
                   (WHERE Status LIKE 'delivered%customer')) AS deltaT
    FROM package_tracking_pti
    GROUP BY TIME(* AND ParcelNumber)
) as t1;

Result:

% FailedDeliveries
20.00%

DELTA_T Function Examples | Teradata Vantage - Examples - Advanced SQL Engine - Teradata Database

Time Series Tables and Operations

Example: Creating the OCEAN_BUOYS_DELTA_T Table

Example: Searching the Minimum and Maximum Observed Temperatures

Example: Creating a Multiset Table to Track Elapsed Time

Example: Finding Time Elapsed between Shipping and Receiving an Item

Example: Tracking an Incorrectly Delivered Package

Example: Tracking Shipping Days and Percentage of Delivery Failures