sql - Split week based on weightage -

i have weights defined below in table.

daynum | day | weight | cumulative weight 1      | mon | 0.3    | 0.3 2      | tue | 0.15   | 0.45 (sum of mon , tues) 3      | wed | 0.1    | 0.55 (sum of mon , tues , wed) 4      | thu | 0.1    | 0.65 5      | fri | 0.15   | 0.8 6      | sat | 0.2    | 1 

and have amounts in table defined @ weekly level (mon - sun) below.

item | date       | amount    | 30-may-16  | 10  ---- week in may , june    | 6-jun-16   | 20    | 13-jun-16  | 30  , on    | 27-jun-16  | 60  ---- week in jun , july 

now want insert table @ daily level, weeks overlapping between 2 different months (in above example - 30 may 5 jun). can explain how can achieve in oracle.

output should below.

item | date       | amount    | 30-may-16  | 4.5     (2 days may mon , tues - calculation 10 * 0.45)    | 1-jun-16   | 5.5     (5 days may rest of week - 10 minus 4.5)    | 6-jun-16   | 20  , on    | 27-jun-16  | 39      (4 days june mon till thurs - calculation 60 * 0.65)    | 1-jul-16   | 21      (3 days july rest of week - 60 minus 39) 

try:

with some_data as(     select a.*,            trunc( trunc( add_months( "date", 1 ), 'mm' ) - "date" )                          days_to_end_of_month,            trunc( add_months( "date", 1 ), 'mm' )                          start_of_next_month     amounts ), some_other_data (       select some_data.*,              case when days_to_end_of_month >= 6 amount                   else ( select some_data.amount * "cumulative weight" weights w                          some_data.days_to_end_of_month = daynum )              end new_amount       some_data ) select item, "date", new_amount amount some_other_data union select item, start_of_next_month, amount-new_amount some_other_data days_to_end_of_month < 6 order "date" ;