|
with prm as (
|
|
select
|
|
b.inv_account_id, b.product_id
|
|
, extract(year from b.balance_date) as yyyy
|
|
, max(b.balance_date) max_balance_date
|
|
from avantrade.trx_ut_account_balances b
|
|
where b.balance_date between '01-JAN-2020' and '30-JAN-2020'
|
|
group by b.inv_account_id, b.product_id, extract(year from b.balance_date)
|
|
)
|
|
, sorted as (
|
|
select
|
|
p.*
|
|
, b.current_unit as latest_unit
|
|
, b.total_cost
|
|
, row_number() over (partition by p.inv_account_id, p.product_id order by p.yyyy desc, p.max_balance_date desc) as seq
|
|
, (case when b.current_unit > 0 then 'YES' else 'NO' end) as has_unit
|
|
from
|
|
prm p
|
|
join trx_ut_account_balances b on
|
|
p.inv_account_id = b.inv_account_id
|
|
and p.product_id = b.product_id
|
|
and p.max_balance_date = b.balance_date
|
|
)
|
|
, pick as (
|
|
select s.*, min(s.seq) over (partition by s.inv_account_id, s.product_id order by s.yyyy desc, s.max_balance_date desc) as min_seq
|
|
from sorted s
|
|
where s.has_unit = 'YES'
|
|
)
|
|
select distinct c.cif, up.product_name, up.currency,p.yyyy as tahun_perolehan,
|
|
--p.max_balance_date,
|
|
p.latest_unit as jumlah_unit, p.total_cost as total_perolehan
|
|
from pick p
|
|
join mst_ut_products up on p.product_id = up.product_id
|
|
join mst_inv_holders h on p.inv_account_Id = h.inv_account_id
|
|
--join MST_INVESTMENT_ACCOUNTS e on b.inv_account_id = e.inv_account_id
|
|
join mst_customers c on h.customer_id = c.customer_id
|
|
where p.min_seq = p.seq
|
|
order by c.cif, up.product_name, up.currency;
|