sqlteradataanalytical

Teradata: Case statement in analytical function


I've a case where the data needs to be represented based on the events

One month snapshot

I need to achieve the following output

Output

Explanation: ABC is represented thrice here and you can see date being different,

Initial Balance is the first_value of the ABC and the date combination.

Opening Balance is as of 01/12/2018

Closing Balance is as of 31/12/2018

Tried this sql but its throwing me Non-aggregate columns must be part of the associated group.

What wrong am doing and any insights on how to achieve the expected output.

select   
id||date as ID,
max(loanamt) over(partition by id,date order by strtdate) as Initial_balance
,max( case when strtdate = '2018-12-01' then balamt else 0 end ) over(partition by id,date order by strtdate ) as Opening_balance
,max(case when strtdate= '2018-12-31' then balamt else 0 end )  over(partition by id,date strtdate desc ) as Closing_balance
from tab
where id = 'ABC'
group by id;

Solution

  • Based on your data and narrative this should return the expected result:

    select   
       id||date_col as ID
      ,loanamt as Initial_balance
      ,max(case when strtdate = DATE '2018-12-01' then balamt else 0 end) as Opening_balance
      ,max(case when strtdate = DATE '2018-12-31' then balamt else 0 end) as Closing_balance
    from tab
    where id = 'ABC'
    group by 1,2;
    

    Simple aggregation, no need for OVER.