Teradata: Merge Rows on date/timestamps

Pradeep

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

Minimun 1 column in the SELECT list must be of PERIOD datatype for Normalize to work.
Normalize operation will be performed on first PERIOD column in the SELECT list.
Can be used in subqueries.
Can be used within SELECT INTO statement.

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

Sample Data: Consider below data showing 2 group of Ids having multiple records over different period.


 Id      type_cd    Start_Dt      End_Dt  media_type_id
---  -----------  ----------  ----------  -------------
  1           11  2009-06-10  2017-07-18              6
  1           11  2017-07-18  2017-08-08              6
  1           11  2017-08-08  2018-08-08              7
  1           11  2018-08-08  9999-12-31              7
  
 Id      type_cd    Start_Dt      End_Dt  media_type_id
---  -----------  ----------  ----------  -------------
  3           11  2009-06-10  2017-07-18              6
  3           11  2017-07-18  2017-08-08              6
  3           11  2017-08-08  2017-08-10              6
  3           11  2017-08-08  2018-08-09              7
  3           11  2017-08-09  2018-08-12              7
  3           11  2018-08-12  2018-08-16              6
  3           11  2018-08-16  9999-12-31              7

Required Output: Merging all rows where end_dt of 1 row meets or overlaps start_dt of next row.
```
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  2017-08-08  2018-08-12              7
 3           11  2018-08-12  2018-08-16              6
 3           11  2018-08-16  9999-12-31              7
```
Result Explanation:
- First 2 rows with Id=1 got merged because end_dt is meeting start_dt for date=2017-07-18 and all other columns values are same.
- 3rd & 4th rows with Id=1 got merged because end_dt is meeting start_dt for date=2018-08-08 and all other columns values are same.
- First 3 rows with Id=3 got merged because end_dt is meeting start_dt for dates 2017-07-18 & 2017-08-08 and all other columns values are same.
- 4th & 5th rows with Id=3 got merged because end_dt is meeting start_dt for date=2018-08-09 and all other columns values are same.
- 6th row with Id=3 does not got merged with any row because end_dt is not meeting any of start_dt.
- 7th row with Id=3 does not got merged with any row because end_dt is not meeting any of start_dt.

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

Step 1: Normalize operation is performed to merge the meeting and overlapping periods(period type can be date/timestamp/time). Below will be the output of the 1st step in the above query:


Id  type_cd  period_datatype               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

Step 2: Extracting the new start and end date for the merged data.

Visit Period datatypes page to learn more about Period datatypes.