Teradata Getting value of a variable from multiple rows

Question

I have the following date and amount variables for a different dates. I need only one row per ID based on minimum of the dates.

Data I have:

 ID     Date         Code      Amt
 101    2/1/2018      A90       25
 101    2/2/2018      B90       15
 101    2/5/2018      A90       25
 102    2/1/2018      B90       10

Data I want:

 ID     A90_Date     B90_Date    A90_Amt  B90_Amt
 101    2/1/2018      2/2/2018       25    15
 102                  2/1/2018             10        

My query:

Select ID, 
    min(case when Code='A90' then Date else Null end) as A90_Date,
    min(case when Code='B90' then Date else Null end) as B90_Date,
    min(case when Code='A90' then Amt else Null end) as A90_Amt,
    min(case when Code='B90' then Amt else Null end) as B90_Amt
  from Table 
  group by 1

Although this gives me the Date and Amount values I am concerned whether they are accurate or not from more than 1MM records. Is there an efficient way to pull this data using Qualify/Partition statements in Teradata?

Thanks for your help in advance.

Answer 1

If the amounts for the same ID/code are different, you might get wrong results. Then you must filter the earliest date before aggregation, e.g.

with cte as
 (
   Select ID, Code, Date, Amt
   from Table 
   qualify row_number() over (partition by ID, Code order by date) = 1
 )   
Select ID, 
    min(case when Code='A90' then Date end) as A90_Date,
    min(case when Code='B90' then Date end) as B90_Date,
    min(case when Code='A90' then Amt  end) as A90_Amt,
    min(case when Code='B90' then Amt  end) as B90_Amt
from cte 
group by 1

Depending on your data this might be more efficient:

Select DISTINCT ID, 
    first_value(case when Code='A90' then Date end) over (partition by ID order by date) as A90_Date,
    first_value(case when Code='B90' then Date end) over (partition by ID order by date) as B90_Date,
    first_value(case when Code='A90' then Amt  end) over (partition by ID order by date) as A90_Amt,
    first_value(case when Code='B90' then Amt  end) over (partition by ID order by date) as B90_Amt  
from Table 
group by 1