CODEWITHSUNDEEP
sqlteradatateradata-sql-assistant
mado
mado

Reputation: 11

Managing historical records with date range SQL

I have a table in Teradata which contain historical data like this one below:

Table_A

A|B|C|  d_d       |   d_f
1|8|2|'2020-01-01'|'2020-02-01'
1|8|2|'2020-02-02'|'2020-03-31'
1|8|3|'2020-04-01'|'2020-05-11'
1|8|3|'2020-05-11'|'9999-12-31'
7|4|5|'2020-07-01'|'2020-09-12'
7|4|5|'2020-09-13'|'9999-12-31'

in output I'm looking for something like this:

    A|B|C|  d_d       |   d_f
    1|8|2|'2020-01-01'|'2020-03-31'
    1|8|3|'2020-04-01'|'9999-12-31'
    7|4|5|'2020-07-01'|'9999-12-31'

I tried this but it miss something to ignore rows ( like row 2,4,6 in the example)

select 
A
,B
,C
,d_d
,case when lead(C)over(partition by a,b order by d_d) <> C 
then cast('9999-12-31' as date)
else lead(d_f)over(partition by a,b order by d_d) end as d_f

from table_a

Upvotes: 0

Views: 92

Answers (2)

dnoeth
dnoeth

Reputation: 60462

There's a nice SQL extension in Teradata to normalize overlapping periods. It works on the datatype PERIOD only, but it can be created on the fly:

with cte as
 (
   select NORMALIZE
      A
     ,B
     ,C
     -- PERIODs are inclusive-exclusive, the -1 adjusts for that
     ,period(d_d -1 , d_f) as pd 
   from table_a
 )
   select
      A
     ,B
     ,C
     ,begin(pd) +1 as d_d -- revert back to inclusive-inclusive
     ,end(pd) as d_f
   from cte

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269763

For the data you have provided, aggregation should work:

select a, b, c, min(d_d), max(d_f)
from a
group by a, b, c;

Upvotes: 0

Related Questions