Group by a set of columns and check if a value corresponding to another column exists in Teradata/SQL

samsamara 0
Viewed 24

Input:

enter image description here

I need to group by (mnth_end_d, id, name) and check if there is a value for debt_calc corresponding to when period_rank = 'T'. And if there's a value, you create this new column debt_exists and populate it with TRUE or FALSE as in the output table below. Any tips on how to do that in Teradata without using any joins (perhaps using partition over)?

Output:

enter image description here

teradata sql
1 Answers

Your requirement translates into a conditional aggregation in SQL:

       -- if there is a value for debt_calc corresponding to when period_rank = 'T'
max(case when period_rank = 'T' and debt_calc is not null 
         then 'TRUE'   -- populate it with TRUE or FALSE 
         else 'FALSE'
    end) 
over (partition by mnth_end_d, id, name) as debt_exists
dnoeth0
Related