Tuesday, July 12, 2022

accumulate specific column in ORACLE and release the value within specific condition

 select id,action_date,action_type,amount,

case when action_type='Cr' then 0 
else sum(amount) over(partition by id,grp,actiontype order by action_date)  
end as acc_amount
from (select id, action_date,action_type, amount, 
      row_number() over (partition by id order by action_date) 
      -row_number() over (partition by id,action_type order by action_date) as grp
      from cashanalysis
     ) t

No comments:

Post a Comment

SQL Query to Convert number into Words for Money

SELECT INITCAP ( DECODE ( FLOOR (TO_NUMBER ( :amount)), 0, '', TO_CHAR (TO_DATE...