'Teradata merge multiple records into single record

I want to merge multiple record into single record Ex: Input

pdt status start_dt end_dt
a Inactive 2022/02/02 2022/02/04
a Inctive 2022/02/05 2022/02/10
a Active 2022/02/10 2022/02/12
b Active 2022/03/13 2022/03/17

Output

pdt status start_dt end_dt
a Active 2022/02/02 2022/02/12
b Active 2022/03/13 2022/03/17
sqlteradata


Solution 1:[1]

Normalize the date ranges first, then join back to pick up the most recent status.

SELECT t1.pdt, t2.status, BEGIN(t1.pd) as start_dt, PRIOR(END(t1.pd)) AS end_dt
FROM (
  SELECT NORMALIZE pdt, PERIOD(start_dt, NEXT(end_dt)) pd 
  FROM mytable ) as t1
JOIN mytable t2
ON t1.pdt = t2.pdt AND PRIOR(END(t1.pd)) = t2.end_dt;

Solution 2:[2]

Assuming start_dt and end_dt are both DATE types:

select pdt, status, min(start_dt), max(end_dt) from your_table
where to_char(start_dt,'YYYY/MM')=to_char(end_dt,'YYYY/MM')
group by pdt, status, to_char(start_dt,'YYYY/MM')

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Fred
Solution 2

Related Questions