Skip to Main Content
  • Questions
  • Split date interval into year slices

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: August 29, 2016 - 10:44 am UTC

Last updated: August 29, 2016 - 1:37 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi,

I need to split a date range into year slices like split 05-May-2015 to 05-May-2016 as
start_date end_date
05_May2015 31-Dec-2015
01-Jan-2016 05-May-2016

Any help would be of great help.

Thankyou

and Connor said...

Here's something to get you started. You probably need to check/adjust it for boundary conditions (eg both dates in the same year etc).

SQL> with t as
  2  ( select
  3      date '1999-05-12' dfrom,
  4      date '2013-07-12' dto
  5    from dual
  6  ),
  7  potential_year_boundaries as
  8  ( select rownum r,
  9           add_months(trunc(dfrom,'YYYY'),12*(level-1)) bnd1,
 10           add_months(trunc(dfrom,'YYYY'),12*level)-1 bnd2,
 11           dfrom, dto
 12    from t
 13    connect by add_months(trunc(dfrom,'YYYY'),12*(level-1)) <= dto
 14  )
 15  select
 16    case
 17      when r = 1 then dfrom
 18      else bnd1
 19    end y_from,
 20    case
 21      when r = count(*) over () then dto
 22      else bnd2
 23    end y_to
 24  from potential_year_boundaries;

Y_FROM    Y_TO
--------- ---------
12-MAY-99 31-DEC-99
01-JAN-00 31-DEC-00
01-JAN-01 31-DEC-01
01-JAN-02 31-DEC-02
01-JAN-03 31-DEC-03
01-JAN-04 31-DEC-04
01-JAN-05 31-DEC-05
01-JAN-06 31-DEC-06
01-JAN-07 31-DEC-07
01-JAN-08 31-DEC-08
01-JAN-09 31-DEC-09
01-JAN-10 31-DEC-10
01-JAN-11 31-DEC-11
01-JAN-12 31-DEC-12
01-JAN-13 12-JUL-13

15 rows selected.


Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, August 29, 2016 - 2:14 pm UTC

This works for a single record.Suppose i have multiple records, the result set went wrong.

with t as
( select 10 as id ,
date '2012-05-12' dfrom,
date '2013-07-12' dto
from dual union all
select 20 as id ,
date '2012-05-12' dfrom,
date '2014-07-12' dto from dual
),
potential_year_boundaries as
( select id,ROW_NUMBER() OVER (PARTITION BY id order by null) r,
add_months(trunc(dfrom,'YYYY'),12*(level-1)) bnd1,
add_months(trunc(dfrom,'YYYY'),12*level)-1 bnd2,
dfrom, dto
from t
connect by add_months(trunc(dfrom,'YYYY'),12*(level-1)) <= dto
)
select id,y_from,y_to from (
select id,
case
when r = 1 then dfrom
else bnd1
end y_from,
case
when r = count(*) over () then dto
else bnd2
end y_to
from potential_year_boundaries) order by id,y_from,y_to ;


Any solution for this please?

for multiple rows

Rajeshwaran Jeyabal, August 30, 2016 - 3:14 am UTC

demo@ORA11G> with t as (
  2  select 10 as id, to_date('12-05-2012','dd-mm-yyyy') as dfrom,
  3     to_date('12-07-2013','dd-mm-yyyy') as dto from dual
  4  union all
  5  select 20 as id, to_date('12-05-2012','dd-mm-yyyy') as dfrom,
  6     to_date('12-07-2014','dd-mm-yyyy') as dto from dual )
  7  select id,dfrom,dto, dt new_start_dt ,
  8      last_day(dt) as new_end_dt , x
  9  from (
 10  select * from t
 11  model
 12    partition by (id)
 13    dimension by ( 1 x )
 14    measures( cast(null as date) dt ,dfrom,dto )
 15    rules iterate(100) until( dt[iteration_number+1] > dto[1] )
 16    ( dt[iteration_number+1] =  trunc( add_months( dfrom[1] , iteration_number ) ,'mm') ,
 17      dfrom[iteration_number+1] = dfrom[1] ,
 18      dto[iteration_number+1] = dto[1] )
 19      )
 20  where dt < dto
 21  order by id, x ;

        ID DFROM       DTO         NEW_START_D NEW_END_DT           X
---------- ----------- ----------- ----------- ----------- ----------
        10 12-MAY-2012 12-JUL-2013 01-MAY-2012 31-MAY-2012          1
        10 12-MAY-2012 12-JUL-2013 01-JUN-2012 30-JUN-2012          2
        10 12-MAY-2012 12-JUL-2013 01-JUL-2012 31-JUL-2012          3
        10 12-MAY-2012 12-JUL-2013 01-AUG-2012 31-AUG-2012          4
        10 12-MAY-2012 12-JUL-2013 01-SEP-2012 30-SEP-2012          5
        10 12-MAY-2012 12-JUL-2013 01-OCT-2012 31-OCT-2012          6
        10 12-MAY-2012 12-JUL-2013 01-NOV-2012 30-NOV-2012          7
        10 12-MAY-2012 12-JUL-2013 01-DEC-2012 31-DEC-2012          8
        10 12-MAY-2012 12-JUL-2013 01-JAN-2013 31-JAN-2013          9
        10 12-MAY-2012 12-JUL-2013 01-FEB-2013 28-FEB-2013         10
        10 12-MAY-2012 12-JUL-2013 01-MAR-2013 31-MAR-2013         11
        10 12-MAY-2012 12-JUL-2013 01-APR-2013 30-APR-2013         12
        10 12-MAY-2012 12-JUL-2013 01-MAY-2013 31-MAY-2013         13
        10 12-MAY-2012 12-JUL-2013 01-JUN-2013 30-JUN-2013         14
        10 12-MAY-2012 12-JUL-2013 01-JUL-2013 31-JUL-2013         15
        20 12-MAY-2012 12-JUL-2014 01-MAY-2012 31-MAY-2012          1
        20 12-MAY-2012 12-JUL-2014 01-JUN-2012 30-JUN-2012          2
        20 12-MAY-2012 12-JUL-2014 01-JUL-2012 31-JUL-2012          3
        20 12-MAY-2012 12-JUL-2014 01-AUG-2012 31-AUG-2012          4
        20 12-MAY-2012 12-JUL-2014 01-SEP-2012 30-SEP-2012          5
        20 12-MAY-2012 12-JUL-2014 01-OCT-2012 31-OCT-2012          6
        20 12-MAY-2012 12-JUL-2014 01-NOV-2012 30-NOV-2012          7
        20 12-MAY-2012 12-JUL-2014 01-DEC-2012 31-DEC-2012          8
        20 12-MAY-2012 12-JUL-2014 01-JAN-2013 31-JAN-2013          9
        20 12-MAY-2012 12-JUL-2014 01-FEB-2013 28-FEB-2013         10
        20 12-MAY-2012 12-JUL-2014 01-MAR-2013 31-MAR-2013         11
        20 12-MAY-2012 12-JUL-2014 01-APR-2013 30-APR-2013         12
        20 12-MAY-2012 12-JUL-2014 01-MAY-2013 31-MAY-2013         13
        20 12-MAY-2012 12-JUL-2014 01-JUN-2013 30-JUN-2013         14
        20 12-MAY-2012 12-JUL-2014 01-JUL-2013 31-JUL-2013         15
        20 12-MAY-2012 12-JUL-2014 01-AUG-2013 31-AUG-2013         16
        20 12-MAY-2012 12-JUL-2014 01-SEP-2013 30-SEP-2013         17
        20 12-MAY-2012 12-JUL-2014 01-OCT-2013 31-OCT-2013         18
        20 12-MAY-2012 12-JUL-2014 01-NOV-2013 30-NOV-2013         19
        20 12-MAY-2012 12-JUL-2014 01-DEC-2013 31-DEC-2013         20
        20 12-MAY-2012 12-JUL-2014 01-JAN-2014 31-JAN-2014         21
        20 12-MAY-2012 12-JUL-2014 01-FEB-2014 28-FEB-2014         22
        20 12-MAY-2012 12-JUL-2014 01-MAR-2014 31-MAR-2014         23
        20 12-MAY-2012 12-JUL-2014 01-APR-2014 30-APR-2014         24
        20 12-MAY-2012 12-JUL-2014 01-MAY-2014 31-MAY-2014         25
        20 12-MAY-2012 12-JUL-2014 01-JUN-2014 30-JUN-2014         26
        20 12-MAY-2012 12-JUL-2014 01-JUL-2014 31-JUL-2014         27

42 rows selected.

demo@ORA11G> with t as (
  2  select 10 as id, to_date('12-05-2012','dd-mm-yyyy') as dfrom,
  3     to_date('12-07-2013','dd-mm-yyyy') as dto from dual
  4  union all
  5  select 20 as id, to_date('12-05-2012','dd-mm-yyyy') as dfrom,
  6     to_date('12-07-2014','dd-mm-yyyy') as dto from dual )
  7  select id, dfrom , dto, trunc( add_months( dfrom,column_value-1),'mm') as new_start_dt,
  8        last_day( add_months( dfrom,column_value-1) ) as new_end_dt, column_value x
  9  from t , table(
 10    cast( multiset(select level
 11    from dual
 12    connect by level <= months_between( trunc(dto,'mm') , trunc(dfrom,'mm'))+1)
 13      as sys.odcinumberlist) ) t2
 14  order by 1, x    ;

        ID DFROM       DTO         NEW_START_D NEW_END_DT           X
---------- ----------- ----------- ----------- ----------- ----------
        10 12-MAY-2012 12-JUL-2013 01-MAY-2012 31-MAY-2012          1
        10 12-MAY-2012 12-JUL-2013 01-JUN-2012 30-JUN-2012          2
        10 12-MAY-2012 12-JUL-2013 01-JUL-2012 31-JUL-2012          3
        10 12-MAY-2012 12-JUL-2013 01-AUG-2012 31-AUG-2012          4
        10 12-MAY-2012 12-JUL-2013 01-SEP-2012 30-SEP-2012          5
        10 12-MAY-2012 12-JUL-2013 01-OCT-2012 31-OCT-2012          6
        10 12-MAY-2012 12-JUL-2013 01-NOV-2012 30-NOV-2012          7
        10 12-MAY-2012 12-JUL-2013 01-DEC-2012 31-DEC-2012          8
        10 12-MAY-2012 12-JUL-2013 01-JAN-2013 31-JAN-2013          9
        10 12-MAY-2012 12-JUL-2013 01-FEB-2013 28-FEB-2013         10
        10 12-MAY-2012 12-JUL-2013 01-MAR-2013 31-MAR-2013         11
        10 12-MAY-2012 12-JUL-2013 01-APR-2013 30-APR-2013         12
        10 12-MAY-2012 12-JUL-2013 01-MAY-2013 31-MAY-2013         13
        10 12-MAY-2012 12-JUL-2013 01-JUN-2013 30-JUN-2013         14
        10 12-MAY-2012 12-JUL-2013 01-JUL-2013 31-JUL-2013         15
        20 12-MAY-2012 12-JUL-2014 01-MAY-2012 31-MAY-2012          1
        20 12-MAY-2012 12-JUL-2014 01-JUN-2012 30-JUN-2012          2
        20 12-MAY-2012 12-JUL-2014 01-JUL-2012 31-JUL-2012          3
        20 12-MAY-2012 12-JUL-2014 01-AUG-2012 31-AUG-2012          4
        20 12-MAY-2012 12-JUL-2014 01-SEP-2012 30-SEP-2012          5
        20 12-MAY-2012 12-JUL-2014 01-OCT-2012 31-OCT-2012          6
        20 12-MAY-2012 12-JUL-2014 01-NOV-2012 30-NOV-2012          7
        20 12-MAY-2012 12-JUL-2014 01-DEC-2012 31-DEC-2012          8
        20 12-MAY-2012 12-JUL-2014 01-JAN-2013 31-JAN-2013          9
        20 12-MAY-2012 12-JUL-2014 01-FEB-2013 28-FEB-2013         10
        20 12-MAY-2012 12-JUL-2014 01-MAR-2013 31-MAR-2013         11
        20 12-MAY-2012 12-JUL-2014 01-APR-2013 30-APR-2013         12
        20 12-MAY-2012 12-JUL-2014 01-MAY-2013 31-MAY-2013         13
        20 12-MAY-2012 12-JUL-2014 01-JUN-2013 30-JUN-2013         14
        20 12-MAY-2012 12-JUL-2014 01-JUL-2013 31-JUL-2013         15
        20 12-MAY-2012 12-JUL-2014 01-AUG-2013 31-AUG-2013         16
        20 12-MAY-2012 12-JUL-2014 01-SEP-2013 30-SEP-2013         17
        20 12-MAY-2012 12-JUL-2014 01-OCT-2013 31-OCT-2013         18
        20 12-MAY-2012 12-JUL-2014 01-NOV-2013 30-NOV-2013         19
        20 12-MAY-2012 12-JUL-2014 01-DEC-2013 31-DEC-2013         20
        20 12-MAY-2012 12-JUL-2014 01-JAN-2014 31-JAN-2014         21
        20 12-MAY-2012 12-JUL-2014 01-FEB-2014 28-FEB-2014         22
        20 12-MAY-2012 12-JUL-2014 01-MAR-2014 31-MAR-2014         23
        20 12-MAY-2012 12-JUL-2014 01-APR-2014 30-APR-2014         24
        20 12-MAY-2012 12-JUL-2014 01-MAY-2014 31-MAY-2014         25
        20 12-MAY-2012 12-JUL-2014 01-JUN-2014 30-JUN-2014         26
        20 12-MAY-2012 12-JUL-2014 01-JUL-2014 31-JUL-2014         27

42 rows selected.

demo@ORA11G>

year intervals expected

A reader, August 30, 2016 - 5:26 am UTC

I am expecting an output of year slices..but teh above solution will give me month intervals. Output something like this:

ID Y_FROM Y_TO
10 12-05-2012 00:00:00 31-12-2012 00:00:00
10 01-01-2013 00:00:00 12-07-2013 00:00:00
20 12-05-2012 00:00:00 31-12-2012 00:00:00
20 01-01-2013 00:00:00 31-12-2013 00:00:00
20 01-01-2014 00:00:00 12-07-2014 00:00:00

year intervals expected

Rajeshwaran Jeyabal, August 30, 2016 - 6:59 am UTC

Just take the above sql's, then you can transform it to any level.

Here are the two combinations (Just watch out for the values under the columns X1 and X2)

demo@ORA11G> with t as (
  2     select 10 as id, to_date('12-05-2012','dd-mm-yyyy') as dfrom,
  3       to_date('12-07-2013','dd-mm-yyyy') as dto from dual
  4     union all
  5     select 20 as id, to_date('12-05-2012','dd-mm-yyyy') as dfrom,
  6       to_date('12-07-2014','dd-mm-yyyy') as dto from dual )
  7  select id, dfrom, dto, min( new_start_dt ), max(new_end_dt) , trunc(new_start_dt,'Y'),
  8       greatest( dfrom ,min( new_start_dt ) )  x1,
  9       least( dto,max(new_end_dt))  x2
 10  from (
 11  select id,dfrom,dto, dt new_start_dt ,
 12     last_day(dt) as new_end_dt , x
 13  from (
 14     select * from t
 15     model
 16     partition by (id)
 17             dimension by ( 1 x )
 18             measures( cast(null as date) dt ,dfrom,dto )
 19     rules iterate(100) until( dt[iteration_number+1] > dto[1] )
 20     (       dt[iteration_number+1] =  trunc( add_months( dfrom[1] , iteration_number ) ,'mm') ,
 21             dfrom[iteration_number+1] = dfrom[1] ,
 22             dto[iteration_number+1] = dto[1] )
 23       )
 24  where dt < dto
 25    )
 26  group by  id, dfrom, dto, trunc(new_start_dt,'Y')
 27  order by 1,4 ;

        ID DFROM      DTO        MIN(NEW_ST MAX(NEW_EN TRUNC(NEW_ X1         X2
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
        10 12-05-2012 12-07-2013 01-05-2012 31-12-2012 01-01-2012 12-05-2012 31-12-2012
        10 12-05-2012 12-07-2013 01-01-2013 31-07-2013 01-01-2013 01-01-2013 12-07-2013
        20 12-05-2012 12-07-2014 01-05-2012 31-12-2012 01-01-2012 12-05-2012 31-12-2012
        20 12-05-2012 12-07-2014 01-01-2013 31-12-2013 01-01-2013 01-01-2013 31-12-2013
        20 12-05-2012 12-07-2014 01-01-2014 31-07-2014 01-01-2014 01-01-2014 12-07-2014

5 rows selected.

demo@ORA11G> with t as (
  2     select 10 as id, to_date('12-05-2012','dd-mm-yyyy') as dfrom,
  3       to_date('12-07-2013','dd-mm-yyyy') as dto from dual
  4     union all
  5     select 20 as id, to_date('12-05-2012','dd-mm-yyyy') as dfrom,
  6       to_date('12-07-2014','dd-mm-yyyy') as dto from dual)
  7  select id,dfrom,dto, dt new_start_dt ,
  8     last_day(dt) as new_end_dt , x,
  9     case when trunc(dfrom,'Y') = trunc(dt,'Y') then greatest( dfrom, dt)
 10               else trunc( greatest( dfrom, dt) ,'Y') end x1 ,
 11     case when trunc(dto,'Y') <> trunc(last_day(dt),'Y') then add_months(  trunc( least( dto , last_day(dt)),'Y'),12)-1
 12               else greatest(dto,last_day(dt)) end x2
 13  from (
 14     select * from t
 15     model
 16     partition by (id)
 17             dimension by ( 1 x )
 18             measures( cast(null as date) dt ,dfrom,dto )
 19     rules iterate(100) until( dt[iteration_number+1] > dto[1] )
 20     (       dt[iteration_number+1] =  trunc( add_months( dfrom[1] , iteration_number*12 ) ,'mm') ,
 21             dfrom[iteration_number+1] = dfrom[1] ,
 22             dto[iteration_number+1] = dto[1] )
 23     )
 24  where dt < dto
 25  order by id, x ;

        ID DFROM      DTO        NEW_START_ NEW_END_DT          X X1         X2
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
        10 12-05-2012 12-07-2013 01-05-2012 31-05-2012          1 12-05-2012 31-12-2012
        10 12-05-2012 12-07-2013 01-05-2013 31-05-2013          2 01-01-2013 12-07-2013
        20 12-05-2012 12-07-2014 01-05-2012 31-05-2012          1 12-05-2012 31-12-2012
        20 12-05-2012 12-07-2014 01-05-2013 31-05-2013          2 01-01-2013 31-12-2013
        20 12-05-2012 12-07-2014 01-05-2014 31-05-2014          3 01-01-2014 12-07-2014

5 rows selected.

demo@ORA11G>

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.