Summary

Derive the last triggering amount based on the following precedence rules.

1. Earliest triggering transaction in the base_key’s period.

2. If no triggering transaction is found in the period_date, select the amount from the earliest triggering transaction of the most recent prior period_date for which one exists.

3. If no triggering transaction has ever existed for a prior period, select the earliest absolute transaction from the period.

• Finally, filter the result to only include the earliest transactions by sequence number for each base_key and period_date.

The provided sql works, but I’m thinking it can be simplified with a clever window function. Can’t figure out how to do it though.

Teradata version 17.20.03.23 . As long as it’s not too DBMS specific I can finagle an answer written for any.

Fiddle for Postgres since Teradata is less widely used:
https://dbfiddle.uk/e-ieQ9ON

Sample data:

Expected

Codes which trigger event : trigger, other trigger

Teradata SQL:

create multiset volatile table test_data 
  (
    unique_key varchar(100),
    base_key varchar(100),
    period_date date,
    sequence integer,
    amount integer,
    code varchar(100)
  ) on commit preserve rows;
  
insert into test_data values ('abc202401011', 'abc','2024-01-01', 1,100,'new');
insert into test_data values ('abc202401012', 'abc','2024-01-01', 2,200,'trigger');
insert into test_data values ('abc202406013', 'abc','2024-06-01', 3,300,'new');
insert into test_data values ('abc202406014', 'abc','2024-06-01', 4,400,'revision');
insert into test_data values ('abc202501011', 'abc','2025-01-01', 5,500,'trigger');
insert into test_data values ('edf202401011', 'edf','2023-01-01', 1,100,'new');
insert into test_data values ('edf202401012', 'edf','2024-01-01', 2,200,'new');

create multiset volatile table triggering_codes 
  ( code varchar(100)
  ) on commit preserve rows;
insert into triggering_codes values ('trigger');
insert into triggering_codes values ('other trigger');

with test_data_tag_trigger as 
  (
    select td1.*,
           case when tc.code is not null then 1
                else 0
            end as code_triggers_event
      from test_data td1
      left
      join triggering_codes tc
        on td1.code = tc.code
  )
select td1.unique_key,
       td1.base_key,
       td1.period_date,
       td1.sequence,
       td2.amount,
       td2.code
  from test_data_tag_trigger td1
  left
  join test_data_tag_trigger td2
    on td1.base_key = td2.base_key
   and td1.sequence >= td2.sequence
   and td1.period_date >= td2.period_date
 qualify row_number() over 
           ( partition by td1.base_key,
                          td1.period_date
                 order by td2.code_triggers_event desc,
                          td2.period_date desc,
                          td2.sequence asc
           ) = 1
 order
    by td1.unique_key,
       td2.unique_key;