drop table tx;
create column table tx as
(select to_varchar(123456) as PO,
200.0 as value,
date'2016-10-05' as start_date,
date'2016-12-11' as end_date
from dummy);
select * from tx;
select distinct
po
, calmonth
-- , full_val
-- , tot_num_days
-- , days_in_month
-- , days_in_month / tot_num_days as mshare
, round(full_val * (days_in_month / tot_num_days), 2) as month_val
from
( select tx.po
, mdays.CALMONTH
, tx.value as full_val
, count(*) over (partition by tx.po, mdays.CALMONTH) as days_in_month
, count(*) over (partition by tx.po) as tot_num_days
from
tx
inner join
"_SYS_BI"."M_TIME_DIMENSION" mdays
on mdays.date_sql between tx.start_date and tx.end_date
) as base
order by po, CALMONTH;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
11 | |
9 | |
7 | |
6 | |
4 | |
4 | |
3 | |
3 | |
3 | |
3 |