Oracle SQL How to break down income by month based on a date range?

Question

Trying to find an efficient way of achieving the results in table B below based on data from table A. Is there an efficient way (i.e. not resource hungry) of doing so assuming one has millions of such records in table A? Please note ID 1 has an end date of 12/31/2199 (not a typo), and we only list the income for each ID during the months of 09/2016 to 12/2016. Also note that ID 3 has two incomes in the month of 11/2016, with 600 representing the November income (since that's the income the ID had at the end of November 2016 month). As for IDs that started in say November 2016, their rows would be missing for Sept 16 and Oct 16 since they did not exist pre-November.

Table A:

ID  INCOME  EFFECTIVE_DATE  END_DATE
1   700     07/01/2016      12/31/2199
2   500     08/20/2016      12/31/2017
3   600     11/11/2016      02/28/2017
3   100     09/01/2016      11/10/2016
4   400     11/21/2016      12/31/2016

Table B (Intended results):

         
ID  INCOME  MONTH
1   700     12/2016
1   700     11/2016
1   700     10/2016
1   700     09/2016
2   500     12/2016
2   500     11/2016
2   500     10/2016
2   500     09/2016
3   600     12/2016
3   600     11/2016
3   100     10/2016
3   100     09/2016
4   400     12/2016
4   400     11/2016

RESOLVED I used the answer provided by @mathguy below and it worked like a charm -- learned something new in this process: pivot and unpivot. Also thanks to @MTO (and everyone else) for taking the time to help.

Answer 1

A solution using a recursive sub-query factoring clause. This does not rely on hard-coding the bounds into the query as they can be passed as the bind variable :lower_bound and :upper_bound; in the example below they are set to DATE '2016-09-01' and DATE '2016-12-31' respectively.

Query:

WITH months ( id, income, month, end_dt ) AS (
  SELECT id,
         income,
         CAST( TRUNC( GREATEST( a.effective_date, :lower_bound ), 'MM' ) AS DATE ),
         LEAST( a.end_date, :upper_bound )
  FROM   TableA a
  WHERE  :lower_bound <= a.end_date
  AND    a.effective_date <= :upper_bound
UNION ALL
  SELECT id,
         income,
         CAST( ADD_MONTHS( month, 1 ) AS DATE ),
         end_dt
  FROM   months
  WHERE  ADD_MONTHS( month, 1 ) <= end_dt
)
SELECT id,
       income,
       LAST_DAY( month ) AS month
FROM   months
WHERE  LAST_DAY( month ) <= end_dt
ORDER BY id, month;

Output:

ID INCOME MONTH
-- ------ ----------
 1    700 2016-09-30
 1    700 2016-10-31
 1    700 2016-11-30
 1    700 2016-12-31
 2    500 2016-09-30
 2    500 2016-10-31
 2    500 2016-11-30
 2    500 2016-12-31
 3    100 2016-09-30
 3    100 2016-10-31
 3    600 2016-11-30
 3    600 2016-12-31
 4    400 2016-11-30
 4    400 2016-12-31

Answer 2

Here is a solution that uses each row from the base table just once, and does not require joins, group by, etc. It uses the unpivot operation, available since Oracle 11.1, which is not an expensive operation.

with
     table_a ( id, income, effective_date, end_date ) as (
       select 1, 700, date '2016-07-01', date '2199-12-31' from dual union all
       select 2, 500, date '2016-08-20', date '2017-12-31' from dual union all
       select 3, 600, date '2016-11-11', date '2017-02-28' from dual union all
       select 3, 100, date '2016-09-01', date '2016-11-10' from dual union all
       select 4, 400, date '2016-11-21', date '2016-12-31' from dual
     )
-- end of test data (not part of the solution): SQL query begins BELOW THIS LINE
select id, income, mth
from (
       select id,
              case when date '2016-09-30' between effective_date and end_date
                   then income end as sep16,
              case when date '2016-10-31' between effective_date and end_date
                   then income end as oct16,
              case when date '2016-11-30' between effective_date and end_date
                   then income end as nov16,
              case when date '2016-12-31' between effective_date and end_date
                   then income end as dec16
       from   table_a
     )
unpivot ( income for mth in ( sep16 as '09/2016', oct16 as '10/2016', nov16 as '11/2016',
                              dec16 as '12/2016' )
        )
order by id, mth desc
;

Output:

ID INCOME MTH
-- ------ -------
 1    700 12/2016
 1    700 11/2016
 1    700 10/2016
 1    700 09/2016
 2    500 12/2016
 2    500 11/2016
 2    500 10/2016
 2    500 09/2016
 3    600 12/2016
 3    600 11/2016
 3    100 10/2016
 3    100 09/2016
 4    400 12/2016
 4    400 11/2016

14 rows selected.