Teradata: Merge Rows on date/timestamps

Teradata: Merge Rows on date/timestamps

This tutorial will explain how to merge rows based on overlapping dates/timestamp using Period datatype. NORMALIZE operation available for period datatypes will be leveraged to merge rows.

Normalize Operation Characteristics
Lets try to understand how row merging will work with the help of a simple example.


Dataset: Lets convert the above dataset into a table for demonstration purpose.
CREATE MULTISET VOLATILE TABLE normalize_example
(
Id              INTEGER,
type_cd         INTEGER,
Start_Dt        DATE,
End_Dt          DATE,
media_type_id   INTEGER
)
ON COMMIT PRESERVE ROWS;


--Data
INSERT INTO normalize_example VALUES(1,11,'2009-06-10','2017-07-18',6);
INSERT INTO normalize_example VALUES(1,11,'2017-07-18','2017-08-08',6);
INSERT INTO normalize_example VALUES(1,11,'2017-08-08','2018-08-08',7);
INSERT INTO normalize_example VALUES(1,11,'2018-08-08','9999-12-31',7);

INSERT INTO normalize_example VALUES(3,11,'2009-06-10','2017-07-18',6);
INSERT INTO normalize_example VALUES(3,11,'2017-07-18','2017-08-08',6);
INSERT INTO normalize_example VALUES(3,11,'2017-08-08','2017-08-10',6);
INSERT INTO normalize_example VALUES(3,11,'2017-08-08','2018-08-09',7);
INSERT INTO normalize_example VALUES(3,11,'2017-08-09','2018-08-12',7);
INSERT INTO normalize_example VALUES(3,11,'2018-08-12','2018-08-16',6);
INSERT INTO normalize_example VALUES(3,11,'2018-08-16','9999-12-31',7);


Query to get merged records: Below query can be leveraged for timestamp columns as well
--Step 2:
SELECT id,
    type_cd,
    BEGIN(period_datatype) start_dt,
    END(period_datatype) end_dt,
    media_type_id
FROM
(
--Step 1:
  SELECT NORMALIZE
    id,
    type_cd,
    PERIOD(start_dt,end_dt) period_datatype,
    media_type_id
  FROM normalize_example
)a;

Output:
 Id      type_cd    start_dt      end_dt  media_type_id
---  -----------  ----------  ----------  -------------
  1           11  2009-06-10  2017-08-08              6
  1           11  2017-08-08  9999-12-31              7
  3           11  2009-06-10  2017-08-10              6
  3           11  2018-08-12  2018-08-16              6
  3           11  2017-08-08  2018-08-12              7
  3           11  2018-08-16  9999-12-31              7
Explanation: Above query is written in 2 steps
Visit Period datatypes page to learn more about Period datatypes.