CODEWITHSUNDEEP
sqloracle-databaseplsql
romeuBraga
romeuBraga

Reputation: 2165

How to split the interval between two dates into details by month?

Sorry for the title ... it's best to describe the problem with an example ...

I have a list of events and two dates for each event and I need to "break" or "distribute" those dates within their respective months.

Example 1:

Event: Event A

Start Date: 12/15/2017 - MM/DD/YYYY

End Date: 01/17/2018 - MM/DD/YYYY

If I do a search on my table for this event, I get a result row with that data.

But I need two results, as shown below:

Result 1: Event A > 15 to 31

Result 2: Event A > 01 to 17

Example 2:

Event: Event B

Start Date: 02/07/2018 - MM/DD/YYYY

End Date: 04/22/2018 - MM/DD/YYYY

Result 1: Event B > 07 to 28

Result 2: Event B > 01 to 31

Result 3: Event B > 01 to 22

What is the most efficient way to do this?

Upvotes: 2

Views: 2167

Answers (3)

ragav
ragav

Reputation: 66

two solutions are available for this question

In Oracle 12C you can use the below query

SELECT DISTINCT e.name,
  CASE
    WHEN e.startdate > x.sdate
    THEN e.startdate
    ELSE x.sdate
  END AS startdate,
  CASE
    WHEN e.enddate < x.edate
    THEN e.enddate
    ELSE x.edate
  END AS enddate
FROM e_vents e CROSS apply
  (SELECT TRUNC( e.startdate, 'mm') + (level-1) * interval '1' MONTH         AS sdate,
    TRUNC( e.startdate              + (level) * interval '1' MONTH, 'mm') -1 AS edate
  FROM e_vents
    CONNECT BY level <= months_between( TRUNC( e.enddate, 'mm'),TRUNC( e.startdate, 'mm')) + 1
  ) x
ORDER BY 1 ASC;

in older versions of oracle use the below query

SELECT e.name,
  greatest(e.startdate,x.sdate) AS startdate,
  least(e.enddate,x.edate)      AS enddate
FROM e_vents e,
  (SELECT TRUNC( e.min_startdate, 'mm') + (level-1) * interval '1' MONTH         AS sdate,
    TRUNC( e.min_startdate              + (level) * interval '1' MONTH, 'mm') -1 AS edate
  FROM
    (SELECT MIN(startdate) min_startdate,MAX(enddate) max_enddate FROM e_vents
    ) e
    CONNECT BY level<= months_between( TRUNC( e.max_enddate, 'mm'),TRUNC( e.min_startdate, 'mm')) + 1
  ) x
WHERE e.startdate BETWEEN x.sdate AND x.edate
OR e.enddate BETWEEN x.sdate AND x.edate
OR x.sdate BETWEEN e.startdate AND e.enddate
OR x.edate BETWEEN e.startdate AND e.enddate
ORDER BY 1 ASC ;

Upvotes: 1

krokodilko
krokodilko

Reputation: 36087

On Oracle 12c cross apply clause can be used:

create table e_vents(
 name varchar2(10),
 startdate date,
 enddate date
);

insert all 
into e_vents values( 'A', date '2017-12-15', date '2018-01-17' )
into e_vents values( 'B', date '2017-12-15', date '2017-12-22' )
into e_vents values( 'C', date '2017-12-15', date '2018-05-22' )
select null from dual;

commit;

select e.name,
       case when e.startdate > x.s_date then e.startdate else x.s_date end as start_date,
       case when e.enddate < x.e_date then e.enddate else x.e_date end as end_date
from e_vents e
cross apply (
  select 
         trunc( e.startdate, 'mm') + (level-1) * interval '1' month as s_date,
         trunc( e.startdate + (level) * interval '1' month, 'mm') -1 as e_date 
  from dual
  connect by level <= months_between( trunc( e.enddate, 'mm'),trunc( e.startdate, 'mm')) + 1
) x

NAME       START_DATE END_DATE        
---------- ---------- ----------
A          2017-12-15 2017-12-31
A          2018-01-01 2018-01-17
B          2017-12-15 2017-12-22
C          2017-12-15 2017-12-31
C          2018-01-01 2018-01-31
C          2018-02-01 2018-02-28
C          2018-03-01 2018-03-31
C          2018-04-01 2018-04-30
C          2018-05-01 2018-05-22

9 rows selected.

Upvotes: 1

Simon Berthiaume
Simon Berthiaume

Reputation: 643

I don't have a full solution for you (if you create a workable SQLFiddle testbench for it, I can probably work it out), but I think it's something requiring a CONNECT BY clause and it would be very close to this solution from Ask Tom.

It goes basically something like this (example from Ask Tom):

variable sdate varchar2(30);
variable edate varchar2(30);
exec :sdate := '01-mar-2011'; :edate := '31-dec-2011';

select level r,
        greatest( add_months(trunc(sdate,'mm'),level-1), sdate ),
        least( last_day( add_months(sdate,level-1) ), edate )
from (select to_date( :sdate, 'dd-mon-yyyy' ) sdate,
                to_date( :edate, 'dd-mon-yyyy' ) edate
           from dual)
connect by level <= months_between( trunc( edate,'mm'), trunc(sdate,'mm') ) + 1;


     R GREATEST( LEAST(LAS
------ --------- ---------
     1 01-MAR-11 31-MAR-11
     2 01-APR-11 30-APR-11
     3 01-MAY-11 31-MAY-11
     4 01-JUN-11 30-JUN-11
     5 01-JUL-11 31-JUL-11
     6 01-AUG-11 31-AUG-11
     7 01-SEP-11 30-SEP-11
     8 01-OCT-11 31-OCT-11
     9 01-NOV-11 30-NOV-11
    10 01-DEC-11 31-DEC-11

10 rows selected.

Upvotes: 1

Related Questions