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
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.
/*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');
/*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;
FailedDeliveries |
---|
2 |
/*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;
FailedDeliveries |
---|
1 |
/*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;
% FailedDeliveries |
---|
20.00% |