We have entered a monthly forecast in the Forecast set. Our MPS is in weekly buckets, How do we convert the monthly forecast so that it is represented in the MPS in weekly buckets?
jan
Here's an example of what you might do. We carve the monthly data into daily amounts, and then sum by week. I've included a number of queries to show how this is built up
SQL> set pages 500
SQL> drop table t purge;
Table dropped.
SQL>
SQL> create table t ( mth date, fcast int );
Table created.
SQL>
SQL> insert into t
2 select add_months(date '2016-01-01',rownum-1), dbms_random.value(1000,5000)
3 from dual
4 connect by level <= 12;
12 rows created.
SQL>
SQL> select * from t order by 1;
MTH FCAST
--------- ----------
01-JAN-16 1979
01-FEB-16 3971
01-MAR-16 4475
01-APR-16 1896
01-MAY-16 1000
01-JUN-16 1021
01-JUL-16 4686
01-AUG-16 4760
01-SEP-16 1047
01-OCT-16 1378
01-NOV-16 4745
01-DEC-16 2588
12 rows selected.
SQL>
SQL> with
2 every_day as
3 ( select date '2016-01-01'+rownum-1 d
4 from dual
5 connect by level <= to_number(to_char(date '2016-12-31','DDD'))
6 )
7 select * from every_day;
D
---------
01-JAN-16
02-JAN-16
03-JAN-16
04-JAN-16
05-JAN-16
06-JAN-16
07-JAN-16
08-JAN-16
09-JAN-16
10-JAN-16
11-JAN-16
12-JAN-16
13-JAN-16
14-JAN-16
15-JAN-16
16-JAN-16
17-JAN-16
18-JAN-16
19-JAN-16
20-JAN-16
21-JAN-16
22-JAN-16
23-JAN-16
24-JAN-16
25-JAN-16
26-JAN-16
27-JAN-16
28-JAN-16
29-JAN-16
30-JAN-16
31-JAN-16
01-FEB-16
02-FEB-16
03-FEB-16
04-FEB-16
05-FEB-16
06-FEB-16
07-FEB-16
08-FEB-16
09-FEB-16
10-FEB-16
11-FEB-16
12-FEB-16
13-FEB-16
14-FEB-16
15-FEB-16
16-FEB-16
17-FEB-16
18-FEB-16
19-FEB-16
20-FEB-16
21-FEB-16
22-FEB-16
23-FEB-16
24-FEB-16
25-FEB-16
26-FEB-16
27-FEB-16
28-FEB-16
29-FEB-16
01-MAR-16
02-MAR-16
03-MAR-16
04-MAR-16
05-MAR-16
06-MAR-16
07-MAR-16
08-MAR-16
09-MAR-16
10-MAR-16
11-MAR-16
12-MAR-16
13-MAR-16
14-MAR-16
15-MAR-16
16-MAR-16
17-MAR-16
18-MAR-16
19-MAR-16
20-MAR-16
21-MAR-16
22-MAR-16
23-MAR-16
24-MAR-16
25-MAR-16
26-MAR-16
27-MAR-16
28-MAR-16
29-MAR-16
30-MAR-16
31-MAR-16
01-APR-16
02-APR-16
03-APR-16
04-APR-16
05-APR-16
06-APR-16
07-APR-16
08-APR-16
09-APR-16
10-APR-16
11-APR-16
12-APR-16
13-APR-16
14-APR-16
15-APR-16
16-APR-16
17-APR-16
18-APR-16
19-APR-16
20-APR-16
21-APR-16
22-APR-16
23-APR-16
24-APR-16
25-APR-16
26-APR-16
27-APR-16
28-APR-16
29-APR-16
30-APR-16
01-MAY-16
02-MAY-16
03-MAY-16
04-MAY-16
05-MAY-16
06-MAY-16
07-MAY-16
08-MAY-16
09-MAY-16
10-MAY-16
11-MAY-16
12-MAY-16
13-MAY-16
14-MAY-16
15-MAY-16
16-MAY-16
17-MAY-16
18-MAY-16
19-MAY-16
20-MAY-16
21-MAY-16
22-MAY-16
23-MAY-16
24-MAY-16
25-MAY-16
26-MAY-16
27-MAY-16
28-MAY-16
29-MAY-16
30-MAY-16
31-MAY-16
01-JUN-16
02-JUN-16
03-JUN-16
04-JUN-16
05-JUN-16
06-JUN-16
07-JUN-16
08-JUN-16
09-JUN-16
10-JUN-16
11-JUN-16
12-JUN-16
13-JUN-16
14-JUN-16
15-JUN-16
16-JUN-16
17-JUN-16
18-JUN-16
19-JUN-16
20-JUN-16
21-JUN-16
22-JUN-16
23-JUN-16
24-JUN-16
25-JUN-16
26-JUN-16
27-JUN-16
28-JUN-16
29-JUN-16
30-JUN-16
01-JUL-16
02-JUL-16
03-JUL-16
04-JUL-16
05-JUL-16
06-JUL-16
07-JUL-16
08-JUL-16
09-JUL-16
10-JUL-16
11-JUL-16
12-JUL-16
13-JUL-16
14-JUL-16
15-JUL-16
16-JUL-16
17-JUL-16
18-JUL-16
19-JUL-16
20-JUL-16
21-JUL-16
22-JUL-16
23-JUL-16
24-JUL-16
25-JUL-16
26-JUL-16
27-JUL-16
28-JUL-16
29-JUL-16
30-JUL-16
31-JUL-16
01-AUG-16
02-AUG-16
03-AUG-16
04-AUG-16
05-AUG-16
06-AUG-16
07-AUG-16
08-AUG-16
09-AUG-16
10-AUG-16
11-AUG-16
12-AUG-16
13-AUG-16
14-AUG-16
15-AUG-16
16-AUG-16
17-AUG-16
18-AUG-16
19-AUG-16
20-AUG-16
21-AUG-16
22-AUG-16
23-AUG-16
24-AUG-16
25-AUG-16
26-AUG-16
27-AUG-16
28-AUG-16
29-AUG-16
30-AUG-16
31-AUG-16
01-SEP-16
02-SEP-16
03-SEP-16
04-SEP-16
05-SEP-16
06-SEP-16
07-SEP-16
08-SEP-16
09-SEP-16
10-SEP-16
11-SEP-16
12-SEP-16
13-SEP-16
14-SEP-16
15-SEP-16
16-SEP-16
17-SEP-16
18-SEP-16
19-SEP-16
20-SEP-16
21-SEP-16
22-SEP-16
23-SEP-16
24-SEP-16
25-SEP-16
26-SEP-16
27-SEP-16
28-SEP-16
29-SEP-16
30-SEP-16
01-OCT-16
02-OCT-16
03-OCT-16
04-OCT-16
05-OCT-16
06-OCT-16
07-OCT-16
08-OCT-16
09-OCT-16
10-OCT-16
11-OCT-16
12-OCT-16
13-OCT-16
14-OCT-16
15-OCT-16
16-OCT-16
17-OCT-16
18-OCT-16
19-OCT-16
20-OCT-16
21-OCT-16
22-OCT-16
23-OCT-16
24-OCT-16
25-OCT-16
26-OCT-16
27-OCT-16
28-OCT-16
29-OCT-16
30-OCT-16
31-OCT-16
01-NOV-16
02-NOV-16
03-NOV-16
04-NOV-16
05-NOV-16
06-NOV-16
07-NOV-16
08-NOV-16
09-NOV-16
10-NOV-16
11-NOV-16
12-NOV-16
13-NOV-16
14-NOV-16
15-NOV-16
16-NOV-16
17-NOV-16
18-NOV-16
19-NOV-16
20-NOV-16
21-NOV-16
22-NOV-16
23-NOV-16
24-NOV-16
25-NOV-16
26-NOV-16
27-NOV-16
28-NOV-16
29-NOV-16
30-NOV-16
01-DEC-16
02-DEC-16
03-DEC-16
04-DEC-16
05-DEC-16
06-DEC-16
07-DEC-16
08-DEC-16
09-DEC-16
10-DEC-16
11-DEC-16
12-DEC-16
13-DEC-16
14-DEC-16
15-DEC-16
16-DEC-16
17-DEC-16
18-DEC-16
19-DEC-16
20-DEC-16
21-DEC-16
22-DEC-16
23-DEC-16
24-DEC-16
25-DEC-16
26-DEC-16
27-DEC-16
28-DEC-16
29-DEC-16
30-DEC-16
31-DEC-16
366 rows selected.
SQL>
SQL>
SQL> with
2 every_day as
3 ( select date '2016-01-01'+rownum-1 d
4 from dual
5 connect by level <= to_number(to_char(date '2016-12-31','DDD'))
6 ),
7 date_ranges as
8 ( select mth, last_day(mth) mth_end, to_number(to_char(last_day(mth),'DD')) no_of_days, fcast
9 from t
10 )
11 select * from date_ranges
12 order by 1;
MTH MTH_END NO_OF_DAYS FCAST
--------- --------- ---------- ----------
01-JAN-16 31-JAN-16 31 1979
01-FEB-16 29-FEB-16 29 3971
01-MAR-16 31-MAR-16 31 4475
01-APR-16 30-APR-16 30 1896
01-MAY-16 31-MAY-16 31 1000
01-JUN-16 30-JUN-16 30 1021
01-JUL-16 31-JUL-16 31 4686
01-AUG-16 31-AUG-16 31 4760
01-SEP-16 30-SEP-16 30 1047
01-OCT-16 31-OCT-16 31 1378
01-NOV-16 30-NOV-16 30 4745
01-DEC-16 31-DEC-16 31 2588
12 rows selected.
SQL>
SQL> with
2 every_day as
3 ( select date '2016-01-01'+rownum-1 d
4 from dual
5 connect by level <= to_number(to_char(date '2016-12-31','DDD'))
6 ),
7 date_ranges as
8 ( select mth, last_day(mth) mth_end, to_number(to_char(last_day(mth),'DD')) no_of_days, fcast
9 from t
10 ),
11 apportioned_forecast as
12 ( select every_day.d, date_ranges.fcast, date_ranges.no_of_days, date_ranges.fcast / date_ranges.no_of_days daily_amount
13 from every_day,
14 date_ranges
15 where every_day.d between date_ranges.mth and date_ranges.mth_end
16 )
17 select * from apportioned_forecast
18 order by 1;
D FCAST NO_OF_DAYS DAILY_AMOUNT
--------- ---------- ---------- ------------
01-JAN-16 1979 31 63.8387097
02-JAN-16 1979 31 63.8387097
03-JAN-16 1979 31 63.8387097
04-JAN-16 1979 31 63.8387097
05-JAN-16 1979 31 63.8387097
06-JAN-16 1979 31 63.8387097
07-JAN-16 1979 31 63.8387097
08-JAN-16 1979 31 63.8387097
09-JAN-16 1979 31 63.8387097
10-JAN-16 1979 31 63.8387097
11-JAN-16 1979 31 63.8387097
12-JAN-16 1979 31 63.8387097
13-JAN-16 1979 31 63.8387097
14-JAN-16 1979 31 63.8387097
15-JAN-16 1979 31 63.8387097
16-JAN-16 1979 31 63.8387097
17-JAN-16 1979 31 63.8387097
18-JAN-16 1979 31 63.8387097
19-JAN-16 1979 31 63.8387097
20-JAN-16 1979 31 63.8387097
21-JAN-16 1979 31 63.8387097
22-JAN-16 1979 31 63.8387097
23-JAN-16 1979 31 63.8387097
24-JAN-16 1979 31 63.8387097
25-JAN-16 1979 31 63.8387097
26-JAN-16 1979 31 63.8387097
27-JAN-16 1979 31 63.8387097
28-JAN-16 1979 31 63.8387097
29-JAN-16 1979 31 63.8387097
30-JAN-16 1979 31 63.8387097
31-JAN-16 1979 31 63.8387097
01-FEB-16 3971 29 136.931034
02-FEB-16 3971 29 136.931034
03-FEB-16 3971 29 136.931034
04-FEB-16 3971 29 136.931034
05-FEB-16 3971 29 136.931034
06-FEB-16 3971 29 136.931034
07-FEB-16 3971 29 136.931034
08-FEB-16 3971 29 136.931034
09-FEB-16 3971 29 136.931034
10-FEB-16 3971 29 136.931034
11-FEB-16 3971 29 136.931034
12-FEB-16 3971 29 136.931034
13-FEB-16 3971 29 136.931034
14-FEB-16 3971 29 136.931034
15-FEB-16 3971 29 136.931034
16-FEB-16 3971 29 136.931034
17-FEB-16 3971 29 136.931034
18-FEB-16 3971 29 136.931034
19-FEB-16 3971 29 136.931034
20-FEB-16 3971 29 136.931034
21-FEB-16 3971 29 136.931034
22-FEB-16 3971 29 136.931034
23-FEB-16 3971 29 136.931034
24-FEB-16 3971 29 136.931034
25-FEB-16 3971 29 136.931034
26-FEB-16 3971 29 136.931034
27-FEB-16 3971 29 136.931034
28-FEB-16 3971 29 136.931034
29-FEB-16 3971 29 136.931034
01-MAR-16 4475 31 144.354839
02-MAR-16 4475 31 144.354839
03-MAR-16 4475 31 144.354839
04-MAR-16 4475 31 144.354839
05-MAR-16 4475 31 144.354839
06-MAR-16 4475 31 144.354839
07-MAR-16 4475 31 144.354839
08-MAR-16 4475 31 144.354839
09-MAR-16 4475 31 144.354839
10-MAR-16 4475 31 144.354839
11-MAR-16 4475 31 144.354839
12-MAR-16 4475 31 144.354839
13-MAR-16 4475 31 144.354839
14-MAR-16 4475 31 144.354839
15-MAR-16 4475 31 144.354839
16-MAR-16 4475 31 144.354839
17-MAR-16 4475 31 144.354839
18-MAR-16 4475 31 144.354839
19-MAR-16 4475 31 144.354839
20-MAR-16 4475 31 144.354839
21-MAR-16 4475 31 144.354839
22-MAR-16 4475 31 144.354839
23-MAR-16 4475 31 144.354839
24-MAR-16 4475 31 144.354839
25-MAR-16 4475 31 144.354839
26-MAR-16 4475 31 144.354839
27-MAR-16 4475 31 144.354839
28-MAR-16 4475 31 144.354839
29-MAR-16 4475 31 144.354839
30-MAR-16 4475 31 144.354839
31-MAR-16 4475 31 144.354839
01-APR-16 1896 30 63.2
02-APR-16 1896 30 63.2
03-APR-16 1896 30 63.2
04-APR-16 1896 30 63.2
05-APR-16 1896 30 63.2
06-APR-16 1896 30 63.2
07-APR-16 1896 30 63.2
08-APR-16 1896 30 63.2
09-APR-16 1896 30 63.2
10-APR-16 1896 30 63.2
11-APR-16 1896 30 63.2
12-APR-16 1896 30 63.2
13-APR-16 1896 30 63.2
14-APR-16 1896 30 63.2
15-APR-16 1896 30 63.2
16-APR-16 1896 30 63.2
17-APR-16 1896 30 63.2
18-APR-16 1896 30 63.2
19-APR-16 1896 30 63.2
20-APR-16 1896 30 63.2
21-APR-16 1896 30 63.2
22-APR-16 1896 30 63.2
23-APR-16 1896 30 63.2
24-APR-16 1896 30 63.2
25-APR-16 1896 30 63.2
26-APR-16 1896 30 63.2
27-APR-16 1896 30 63.2
28-APR-16 1896 30 63.2
29-APR-16 1896 30 63.2
30-APR-16 1896 30 63.2
01-MAY-16 1000 31 32.2580645
02-MAY-16 1000 31 32.2580645
03-MAY-16 1000 31 32.2580645
04-MAY-16 1000 31 32.2580645
05-MAY-16 1000 31 32.2580645
06-MAY-16 1000 31 32.2580645
07-MAY-16 1000 31 32.2580645
08-MAY-16 1000 31 32.2580645
09-MAY-16 1000 31 32.2580645
10-MAY-16 1000 31 32.2580645
11-MAY-16 1000 31 32.2580645
12-MAY-16 1000 31 32.2580645
13-MAY-16 1000 31 32.2580645
14-MAY-16 1000 31 32.2580645
15-MAY-16 1000 31 32.2580645
16-MAY-16 1000 31 32.2580645
17-MAY-16 1000 31 32.2580645
18-MAY-16 1000 31 32.2580645
19-MAY-16 1000 31 32.2580645
20-MAY-16 1000 31 32.2580645
21-MAY-16 1000 31 32.2580645
22-MAY-16 1000 31 32.2580645
23-MAY-16 1000 31 32.2580645
24-MAY-16 1000 31 32.2580645
25-MAY-16 1000 31 32.2580645
26-MAY-16 1000 31 32.2580645
27-MAY-16 1000 31 32.2580645
28-MAY-16 1000 31 32.2580645
29-MAY-16 1000 31 32.2580645
30-MAY-16 1000 31 32.2580645
31-MAY-16 1000 31 32.2580645
01-JUN-16 1021 30 34.0333333
02-JUN-16 1021 30 34.0333333
03-JUN-16 1021 30 34.0333333
04-JUN-16 1021 30 34.0333333
05-JUN-16 1021 30 34.0333333
06-JUN-16 1021 30 34.0333333
07-JUN-16 1021 30 34.0333333
08-JUN-16 1021 30 34.0333333
09-JUN-16 1021 30 34.0333333
10-JUN-16 1021 30 34.0333333
11-JUN-16 1021 30 34.0333333
12-JUN-16 1021 30 34.0333333
13-JUN-16 1021 30 34.0333333
14-JUN-16 1021 30 34.0333333
15-JUN-16 1021 30 34.0333333
16-JUN-16 1021 30 34.0333333
17-JUN-16 1021 30 34.0333333
18-JUN-16 1021 30 34.0333333
19-JUN-16 1021 30 34.0333333
20-JUN-16 1021 30 34.0333333
21-JUN-16 1021 30 34.0333333
22-JUN-16 1021 30 34.0333333
23-JUN-16 1021 30 34.0333333
24-JUN-16 1021 30 34.0333333
25-JUN-16 1021 30 34.0333333
26-JUN-16 1021 30 34.0333333
27-JUN-16 1021 30 34.0333333
28-JUN-16 1021 30 34.0333333
29-JUN-16 1021 30 34.0333333
30-JUN-16 1021 30 34.0333333
01-JUL-16 4686 31 151.16129
02-JUL-16 4686 31 151.16129
03-JUL-16 4686 31 151.16129
04-JUL-16 4686 31 151.16129
05-JUL-16 4686 31 151.16129
06-JUL-16 4686 31 151.16129
07-JUL-16 4686 31 151.16129
08-JUL-16 4686 31 151.16129
09-JUL-16 4686 31 151.16129
10-JUL-16 4686 31 151.16129
11-JUL-16 4686 31 151.16129
12-JUL-16 4686 31 151.16129
13-JUL-16 4686 31 151.16129
14-JUL-16 4686 31 151.16129
15-JUL-16 4686 31 151.16129
16-JUL-16 4686 31 151.16129
17-JUL-16 4686 31 151.16129
18-JUL-16 4686 31 151.16129
19-JUL-16 4686 31 151.16129
20-JUL-16 4686 31 151.16129
21-JUL-16 4686 31 151.16129
22-JUL-16 4686 31 151.16129
23-JUL-16 4686 31 151.16129
24-JUL-16 4686 31 151.16129
25-JUL-16 4686 31 151.16129
26-JUL-16 4686 31 151.16129
27-JUL-16 4686 31 151.16129
28-JUL-16 4686 31 151.16129
29-JUL-16 4686 31 151.16129
30-JUL-16 4686 31 151.16129
31-JUL-16 4686 31 151.16129
01-AUG-16 4760 31 153.548387
02-AUG-16 4760 31 153.548387
03-AUG-16 4760 31 153.548387
04-AUG-16 4760 31 153.548387
05-AUG-16 4760 31 153.548387
06-AUG-16 4760 31 153.548387
07-AUG-16 4760 31 153.548387
08-AUG-16 4760 31 153.548387
09-AUG-16 4760 31 153.548387
10-AUG-16 4760 31 153.548387
11-AUG-16 4760 31 153.548387
12-AUG-16 4760 31 153.548387
13-AUG-16 4760 31 153.548387
14-AUG-16 4760 31 153.548387
15-AUG-16 4760 31 153.548387
16-AUG-16 4760 31 153.548387
17-AUG-16 4760 31 153.548387
18-AUG-16 4760 31 153.548387
19-AUG-16 4760 31 153.548387
20-AUG-16 4760 31 153.548387
21-AUG-16 4760 31 153.548387
22-AUG-16 4760 31 153.548387
23-AUG-16 4760 31 153.548387
24-AUG-16 4760 31 153.548387
25-AUG-16 4760 31 153.548387
26-AUG-16 4760 31 153.548387
27-AUG-16 4760 31 153.548387
28-AUG-16 4760 31 153.548387
29-AUG-16 4760 31 153.548387
30-AUG-16 4760 31 153.548387
31-AUG-16 4760 31 153.548387
01-SEP-16 1047 30 34.9
02-SEP-16 1047 30 34.9
03-SEP-16 1047 30 34.9
04-SEP-16 1047 30 34.9
05-SEP-16 1047 30 34.9
06-SEP-16 1047 30 34.9
07-SEP-16 1047 30 34.9
08-SEP-16 1047 30 34.9
09-SEP-16 1047 30 34.9
10-SEP-16 1047 30 34.9
11-SEP-16 1047 30 34.9
12-SEP-16 1047 30 34.9
13-SEP-16 1047 30 34.9
14-SEP-16 1047 30 34.9
15-SEP-16 1047 30 34.9
16-SEP-16 1047 30 34.9
17-SEP-16 1047 30 34.9
18-SEP-16 1047 30 34.9
19-SEP-16 1047 30 34.9
20-SEP-16 1047 30 34.9
21-SEP-16 1047 30 34.9
22-SEP-16 1047 30 34.9
23-SEP-16 1047 30 34.9
24-SEP-16 1047 30 34.9
25-SEP-16 1047 30 34.9
26-SEP-16 1047 30 34.9
27-SEP-16 1047 30 34.9
28-SEP-16 1047 30 34.9
29-SEP-16 1047 30 34.9
30-SEP-16 1047 30 34.9
01-OCT-16 1378 31 44.4516129
02-OCT-16 1378 31 44.4516129
03-OCT-16 1378 31 44.4516129
04-OCT-16 1378 31 44.4516129
05-OCT-16 1378 31 44.4516129
06-OCT-16 1378 31 44.4516129
07-OCT-16 1378 31 44.4516129
08-OCT-16 1378 31 44.4516129
09-OCT-16 1378 31 44.4516129
10-OCT-16 1378 31 44.4516129
11-OCT-16 1378 31 44.4516129
12-OCT-16 1378 31 44.4516129
13-OCT-16 1378 31 44.4516129
14-OCT-16 1378 31 44.4516129
15-OCT-16 1378 31 44.4516129
16-OCT-16 1378 31 44.4516129
17-OCT-16 1378 31 44.4516129
18-OCT-16 1378 31 44.4516129
19-OCT-16 1378 31 44.4516129
20-OCT-16 1378 31 44.4516129
21-OCT-16 1378 31 44.4516129
22-OCT-16 1378 31 44.4516129
23-OCT-16 1378 31 44.4516129
24-OCT-16 1378 31 44.4516129
25-OCT-16 1378 31 44.4516129
26-OCT-16 1378 31 44.4516129
27-OCT-16 1378 31 44.4516129
28-OCT-16 1378 31 44.4516129
29-OCT-16 1378 31 44.4516129
30-OCT-16 1378 31 44.4516129
31-OCT-16 1378 31 44.4516129
01-NOV-16 4745 30 158.166667
02-NOV-16 4745 30 158.166667
03-NOV-16 4745 30 158.166667
04-NOV-16 4745 30 158.166667
05-NOV-16 4745 30 158.166667
06-NOV-16 4745 30 158.166667
07-NOV-16 4745 30 158.166667
08-NOV-16 4745 30 158.166667
09-NOV-16 4745 30 158.166667
10-NOV-16 4745 30 158.166667
11-NOV-16 4745 30 158.166667
12-NOV-16 4745 30 158.166667
13-NOV-16 4745 30 158.166667
14-NOV-16 4745 30 158.166667
15-NOV-16 4745 30 158.166667
16-NOV-16 4745 30 158.166667
17-NOV-16 4745 30 158.166667
18-NOV-16 4745 30 158.166667
19-NOV-16 4745 30 158.166667
20-NOV-16 4745 30 158.166667
21-NOV-16 4745 30 158.166667
22-NOV-16 4745 30 158.166667
23-NOV-16 4745 30 158.166667
24-NOV-16 4745 30 158.166667
25-NOV-16 4745 30 158.166667
26-NOV-16 4745 30 158.166667
27-NOV-16 4745 30 158.166667
28-NOV-16 4745 30 158.166667
29-NOV-16 4745 30 158.166667
30-NOV-16 4745 30 158.166667
01-DEC-16 2588 31 83.483871
02-DEC-16 2588 31 83.483871
03-DEC-16 2588 31 83.483871
04-DEC-16 2588 31 83.483871
05-DEC-16 2588 31 83.483871
06-DEC-16 2588 31 83.483871
07-DEC-16 2588 31 83.483871
08-DEC-16 2588 31 83.483871
09-DEC-16 2588 31 83.483871
10-DEC-16 2588 31 83.483871
11-DEC-16 2588 31 83.483871
12-DEC-16 2588 31 83.483871
13-DEC-16 2588 31 83.483871
14-DEC-16 2588 31 83.483871
15-DEC-16 2588 31 83.483871
16-DEC-16 2588 31 83.483871
17-DEC-16 2588 31 83.483871
18-DEC-16 2588 31 83.483871
19-DEC-16 2588 31 83.483871
20-DEC-16 2588 31 83.483871
21-DEC-16 2588 31 83.483871
22-DEC-16 2588 31 83.483871
23-DEC-16 2588 31 83.483871
24-DEC-16 2588 31 83.483871
25-DEC-16 2588 31 83.483871
26-DEC-16 2588 31 83.483871
27-DEC-16 2588 31 83.483871
28-DEC-16 2588 31 83.483871
29-DEC-16 2588 31 83.483871
30-DEC-16 2588 31 83.483871
31-DEC-16 2588 31 83.483871
366 rows selected.
SQL>
SQL>
SQL> with
2 every_day as
3 ( select date '2016-01-01'+rownum-1 d
4 from dual
5 connect by level <= to_number(to_char(date '2016-12-31','DDD'))
6 ),
7 date_ranges as
8 ( select mth, last_day(mth) mth_end, to_number(to_char(last_day(mth),'DD')) no_of_days, fcast
9 from t
10 ),
11 apportioned_forecast as
12 ( select every_day.d, date_ranges.fcast, date_ranges.no_of_days
13 from every_day,
14 date_ranges
15 where every_day.d between date_ranges.mth and date_ranges.mth_end
16 )
17 select trunc(d,'IW') wk, round(sum(fcast/no_of_days),2) wk_fcast
18 from apportioned_forecast
19 group by trunc(d,'IW')
20 order by 1;
WK WK_FCAST
--------- ----------
28-DEC-15 191.52
04-JAN-16 446.87
11-JAN-16 446.87
18-JAN-16 446.87
25-JAN-16 446.87
01-FEB-16 958.52
08-FEB-16 958.52
15-FEB-16 958.52
22-FEB-16 958.52
29-FEB-16 1003.06
07-MAR-16 1010.48
14-MAR-16 1010.48
21-MAR-16 1010.48
28-MAR-16 767.02
04-APR-16 442.4
11-APR-16 442.4
18-APR-16 442.4
25-APR-16 411.46
02-MAY-16 225.81
09-MAY-16 225.81
16-MAY-16 225.81
23-MAY-16 225.81
30-MAY-16 234.68
06-JUN-16 238.23
13-JUN-16 238.23
20-JUN-16 238.23
27-JUN-16 589.62
04-JUL-16 1058.13
11-JUL-16 1058.13
18-JUL-16 1058.13
25-JUL-16 1058.13
01-AUG-16 1074.84
08-AUG-16 1074.84
15-AUG-16 1074.84
22-AUG-16 1074.84
29-AUG-16 600.25
05-SEP-16 244.3
12-SEP-16 244.3
19-SEP-16 244.3
26-SEP-16 263.4
03-OCT-16 311.16
10-OCT-16 311.16
17-OCT-16 311.16
24-OCT-16 311.16
31-OCT-16 993.45
07-NOV-16 1107.17
14-NOV-16 1107.17
21-NOV-16 1107.17
28-NOV-16 808.44
05-DEC-16 584.39
12-DEC-16 584.39
19-DEC-16 584.39
26-DEC-16 500.9
53 rows selected.
SQL>