I would like to get a table of months between two dates with a fraction of each month that the two dates cover.
For example with a start date of 15/01/2017 and end date of 01/03/2017 it would output:
01/2017 : 0.5483..
02/2017 : 1
03/2017: 0.0322..
where for January and March the calculations are 17/31 and 1/31 respectively. I currently have the query:
WITH dates_between as (SELECT ADD_MONTHS(TRUNC(TO_DATE(:givenStartDate,'dd/mm/yyyy'), 'MON'), ROWNUM - 1) date_out
FROM DUAL
CONNECT BY ADD_MONTHS(TRUNC(TO_DATE(:givenStartDate,'dd/mm/yyyy'), 'MON'), ROWNUM - 1)
<= TRUNC(TO_DATE(:givenEndDate,'dd/mm/yyyy'), 'MON')
)
select * from dates_between
This outputs each month between two dates and formats it to the start of the month. I just need another column to give me the fraction the start and end dates cover. I'm not sure of a way to do this without it getting messy.
The months_between() function "calculates the fractional portion of the result based on a 31-day month". That means that if your range starts or ends in a month that doesn't have 31 days, the fraction you get might not be quite what you expect:
select months_between(date '2017-04-02', date '2017-04-01') as calc from dual
CALC
----------
.0322580645
... which is 1/31, not 1/30. To get 0.0333... instead you'd need to calculate the number of days in each month, at least for the first and last month. This uses a recursive CTE (11gR2+) to get the months, using a couple of date ranges provided by another CTE as a demo to show the difference (you can use a hierarchical query too of course):
with ranges (id, start_date, end_date) as (
select 1, date '2017-01-15', date '2017-03-01' from dual
union all select 2, date '2017-01-31', date '2017-03-01' from dual
union all select 3, date '2017-02-28', date '2017-04-01' from dual
),
months (id, month_start, month_days, range_start, range_end) as (
select id,
trunc(start_date, 'MM'),
extract(day from last_day(start_date)),
start_date,
end_date
from ranges
union all
select id,
month_start + interval '1' month,
extract(day from last_day(month_start + interval '1' month)),
range_start,
range_end
from months
where month_start < range_end
)
select id,
to_char(month_start, 'YYYY-MM-DD') as month_start,
month_days,
case when month_start = trunc(range_start, 'MM')
then month_days - extract(day from range_start) + 1
when month_start = trunc(range_end, 'MM')
then extract(day from range_end)
else month_days end as range_days,
(case when month_start = trunc(range_start, 'MM')
then month_days - extract(day from range_start) + 1
when month_start = trunc(range_end, 'MM')
then extract(day from range_end)
else month_days end) / month_days as fraction
from months
order by id, month_start;
which gets:
ID MONTH_STAR MONTH_DAYS RANGE_DAYS FRACTION
------ ---------- ---------- ---------- --------
1 2017-01-01 31 17 0.5483
1 2017-02-01 28 28 1
1 2017-03-01 31 1 0.0322
2 2017-01-01 31 1 0.0322
2 2017-02-01 28 28 1
2 2017-03-01 31 1 0.0322
3 2017-02-01 28 1 0.0357
3 2017-03-01 31 31 1
3 2017-04-01 30 1 0.0333
The first CTE ranges is just the demo data. The second, recursive, CTE months generates the start and number of days in each month, while keeping track of the original range dates too. The final query just calculates the fractions based on the number of days in the month in the range against the number of days in that month overall.
The month_days and range_days are only shown in the output so you can see what the calculation is based on, you can obviously omit those from your actual result, and format the month start date however you want.
With your original single pair of bind variables the equivalent would be:
with months (month_start, month_days, range_start, range_end) as (
select trunc(to_date(:givenstartdate, 'DD/MM/YYYY'), 'MM'),
extract(day from last_day(to_date(:givenstartdate, 'DD/MM/YYYY'))),
to_date(:givenstartdate, 'DD/MM/YYYY'),
to_date(:givenenddate, 'DD/MM/YYYY')
from dual
union all
select month_start + interval '1' month,
extract(day from last_day(month_start + interval '1' month)),
range_start,
range_end
from months
where month_start < range_end
)
select to_char(month_start, 'MM/YYYY') as month,
(case when month_start = trunc(range_start, 'MM')
then month_days - extract(day from range_start) + 1
when month_start = trunc(range_end, 'MM')
then extract(day from range_end)
else month_days end) / month_days as fraction
from months
order by month_start;
MONTH FRACTION
------- --------
01/2017 0.5483
02/2017 1
03/2017 0.0322