CODEWITHSUNDEEP
sqloracle-databasedate
wasp256
wasp256

Reputation: 6252

oracle sql statement get months between two dates

I have a table in with a start date and an end date

    Table A
    id   |   begin    |   end
    ------------------------
    1    | 15-03-2014 | 06-05-2014
    2    | 03-04-2014 | 31-04-2014

And I need a result that selects the weeks beetween those dates:

   Result should return the rows:
   id       |    month    |    begin     |   end
   ---------------------------------------------
   1        | 03/03/2014  |  15-03-2014  | 06-05-2014 
   1        | 04/03/2014  |  15-03-2014  | 06-05-2014 
   1        | 05/03/2014  |  15-03-2014  | 06-05-2014  
   1        | 06/03/2014  |  15-03-2014  | 06-05-2014
   1        | 01/04/2014  |  03-04-2014  | 31-04-2014  
   ...
   2        | 01/04/2014  |  03-04-2014  | 31-04-2014 
   2        | 02/04/2014  |  03-04-2014  | 31-04-2014 
   2        | 03/04/2014  |  03-04-2014  | 31-04-2014 
   2        | 04/04/2014  |  03-04-2014  | 31-04-2014 
   2        | 05/04/2014  |  03-04-2014  | 31-04-2014

Or for months:

   id       |    month    |    begin     |   end
   ---------------------------------------------
   1        | 03/2014     |  15-03-2014  | 06-05-2014 
   1        | 04/2014     |  15-03-2014  | 06-05-2014 
   1        | 05/2014     |  15-03-2014  | 06-05-2014  
   2        | 04/2014     |  03-04-2014  | 31-04-2014

The selects for weeks/months can be seperate not all in one!

Upvotes: 0

Views: 588

Answers (3)

René Nyffenegger
René Nyffenegger

Reputation: 40603

with i (id, month, begin_, end_) as (
     select 
       id, 
       trunc(begin_, 'mm') month, 
       begin_, 
       end_ 
     from   
       a 
  union all 
     select 
       id, 
       add_months(month, 1) month, 
       begin_, 
       end_ 
     from   
       i 
     where
       add_months(month, 1) < end_
) 
select
  id, 
  to_char(month, 'mm/yyyy') month, 
  begin_, 
  end_ 
from
  i 
order  by 
  id, 
  month;

Upvotes: 1

Dmitry Nikiforov
Dmitry Nikiforov

Reputation: 3038

SQL> with t (id, begin#, end#)
  2  as
  3  (
  4  select 1, to_date('15-03-2014','DD-MM-YYYY'), to_date('06-05-2014','DD-MM-YYYY') from dual
  5  union all
  6  select 2, to_date('03-04-2014','DD-MM-YYYY'), to_date('30-04-2014','DD-MM-YYYY') from dual
  7  )
  8  ,
  9  t1 (id, step#, begin#, end#) as
 10  (
 11  select id, begin#, begin#, end# from t
 12  union all
 13  select id, step#+1, begin#, end# from t1 where step# < end#
 14  )
 15  select * from t1
 16  order by id, step#
 17  /

  ID STEP#    BEGIN#   END#                                                     
---- -------- -------- --------                                                 
   1 15.03.14 15.03.14 06.05.14                                                 
   1 16.03.14 15.03.14 06.05.14                                                 
   1 17.03.14 15.03.14 06.05.14                                                 
   1 18.03.14 15.03.14 06.05.14                                                 
   ............................
   1 02.05.14 15.03.14 06.05.14                                                 
   1 03.05.14 15.03.14 06.05.14                                                 
   1 04.05.14 15.03.14 06.05.14                                                 
   1 05.05.14 15.03.14 06.05.14                                                 
   1 06.05.14 15.03.14 06.05.14

   2 03.04.14 03.04.14 30.04.14                                                 
   2 04.04.14 03.04.14 30.04.14                                                 
   2 05.04.14 03.04.14 30.04.14                                                 
   2 06.04.14 03.04.14 30.04.14                                                 
   2 07.04.14 03.04.14 30.04.14                                                 
   ............................
   2 27.04.14 03.04.14 30.04.14                                                 
   2 28.04.14 03.04.14 30.04.14                                                 
   2 29.04.14 03.04.14 30.04.14                                                 
   2 30.04.14 03.04.14 30.04.14




SQL> with t (id, begin#, end#)
  2  as
  3  (
  4  select 1, to_date('15-03-2014','DD-MM-YYYY'), to_date('06-05-2014','DD-MM-YYYY') from dual
  5  union all
  6  select 2, to_date('03-04-2014','DD-MM-YYYY'), to_date('30-04-2014','DD-MM-YYYY') from dual
  7  )
  8  ,
  9  t1 (id, step#, begin#, end#) as
 10  (
 11  select id, begin#, begin#, end# from t
 12  union all
 13  select id, step#+1, begin#, end# from t1 where step# < end#
 14  )
 15  select unique id, to_char(trunc(step#,'MM'),'MM/YYYY') step#, begin#, end# from t1
 16  order by id, step#
 17  /

  ID STEP#   BEGIN#   END#                                                      
---- ------- -------- --------                                                  
   1 03/2014 15.03.14 06.05.14                                                  
   1 04/2014 15.03.14 06.05.14                                                  
   1 05/2014 15.03.14 06.05.14                                                  
   2 04/2014 03.04.14 30.04.14

Upvotes: 1

Chirag Sutariya
Chirag Sutariya

Reputation: 362

select  TRUNC(MONTHS_BETWEEN(end, begin)) as NumOfMonths, begin, end from TableA

Upvotes: 0

Related Questions