Teradata: Case statement in analytical function

Question

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

I need to achieve the following 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;

Answer 1

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.