0

I have a simple select query that has this result:

first_date | last_date  | outstanding
14/01/2015 | 14/04/2015 | 100000

I want to split it to be 

first_date | last_date  | period     | outstanding 
14/01/2015 | 31/01/2015 | 31/01/2015 | 100000 
01/02/2015 | 28/02/2015 | 28/02/2015 | 100000
01/03/2015 | 31/03/2015 | 31/03/2015 | 100000
01/04/2015 | 14/04/2015 | 31/04/2015 | 100000

Please show me how to do it simply, without using function/procedure, object and cursor.

Gary_W
  • 9,933
  • 1
  • 22
  • 40
toMuchArgs
  • 53
  • 6
  • You can check this - http://stackoverflow.com/questions/20269917/split-date-range-into-one-row-per-month-in-sql-server – Shankar Jun 14 '16 at 19:18
  • 1
    Please search stachoverflow next time before you post a question, you'd be amazed how many times the same question is asked by different people – Shankar Jun 14 '16 at 19:19
  • sorry, my english is not good, i have hard time to find the keyword for googling it, thanks – toMuchArgs Jun 14 '16 at 19:28
  • i dont understand it, so i simply copy paste it in my toad and it show error PLS-00103 encountered the symbol @ – toMuchArgs Jun 14 '16 at 19:46

1 Answers1

1

Try:

WITH my_query_result AS(
  SELECT date '2015-01-14' as first_date , date '2015-04-14' as last_date,
         10000 as outstanding
  FROM dual
)
SELECT  greatest( trunc( add_months( first_date, level - 1 ),'MM'), first_date ) 
           as first_date,
        least( trunc( add_months( first_date, level ),'MM')-1, last_date ) 
           as last_date,
        trunc( add_months( first_date, level ),'MM')-1 as period,
        outstanding
FROM my_query_result t
connect by level <= months_between( trunc(last_date,'MM'), trunc(first_date,'MM') ) + 1;

A side note: April has only 30 days, so a date 31/04/2015 in your question is wrong.

krokodilko
  • 35,300
  • 7
  • 55
  • 79