How to calculate Quarters which are exist in an Intervall represented by Date Values?

Question

I have the following database table (I use an Oracle database):

Is there a mistake inside the calculation? It is possible to solve this problem?

Answer 1

How about this? Lines #1 - 5 represent sample data (you already have that), so query you'd need begins at line #7.

• if beginn_date doesn't belong to month in which certain quarter begins, truncate it to q(uarter) (line #8)
  • previous "solution" truncated it to mm, which is the 1st day in that month
• if there's no end_date, use nvl function with sysdate (line #12)

---

SQL> with test (id, beginn_date, end_date) as
  2    (select 114, date '2019-07-11', date '2019-09-30' from dual union all
  3     select 115, date '2019-10-01', date '2020-02-05' from dual union all
  4     select 116, date '2018-04-15', date '2020-02-05' from dual union all
  5     select 117, date '2019-04-03', null              from dual
  6    )
  7  select id,
  8         add_months(trunc(beginn_date, 'q'), 3 * (column_value - 1)) result
  9  from test cross join
 10       table(cast(multiset(select level from dual
 11                           connect by level <=
 12                           months_between(trunc(nvl(end_date, sysdate), 'mm'),
 13                                          trunc(beginn_date, 'mm')
 14                                         ) / 3 + 1
 15                           ) as sys.odcinumberlist))
 16  order by id, result;

        ID RESULT
---------- ----------
       114 01.07.2019
       115 01.10.2019
       115 01.01.2020
       116 01.04.2018
       116 01.07.2018
       116 01.10.2018
       116 01.01.2019
       116 01.04.2019
       116 01.07.2019
       116 01.10.2019
       116 01.01.2020
       117 01.04.2019
       117 01.07.2019
       117 01.10.2019
       117 01.01.2020

15 rows selected.

SQL>

---

If you want to avoid code specific to Oracle (I presume you don't like line #10 at all - table(cast(multiset(...), the result would be the same if you used

SQL> with test (id, beginn_date, end_date) as
  2    (select 114, date '2019-07-11', date '2019-09-30' from dual union all
  3     select 115, date '2019-10-01', date '2020-02-05' from dual union all
  4     select 116, date '2018-04-15', date '2020-02-05' from dual union all
  5     select 117, date '2019-04-03', null              from dual
  6    )
  7  select distinct
  8         id,
  9         add_months(trunc(beginn_date, 'q'), 3 * (level - 1)) result
 10  from test
 11  connect by level <= months_between(trunc(nvl(end_date, sysdate), 'mm'),
 12                                     trunc(beginn_date, 'mm')
 13                                    ) / 3 + 1
 14  order by id, result;

Note line #7 which uses distinct. If you remove it, you'll get ~300 rows, many of them being duplicates. Code I initially posted handles that, and handles it better than distinct does. For small data sets you won't notice the difference, but certainly will if you have to deal with a lot of data.

Answer 2

If I'm correct, what you want is a list of quarters that your data currently overlaps with.

The simplest from my perspective is to use a calendar table. Populated with all the dates that are of interest to you. (There are many uses for numbers tables or calendar tables, I'll leave that for you to research separately.)

A quick and simple example, just for this question, could be as follows:

CREATE TABLE calendar_quarters (
    interval_start    DATE,
    interval_cease    DATE
);

INSERT INTO calendar_quarters VALUES ('2018-01-01', '2018-04-01');
INSERT INTO calendar_quarters VALUES ('2018-04-01', '2018-07-01');
INSERT INTO calendar_quarters VALUES ('2018-07-01', '2018-10-01');
INSERT INTO calendar_quarters VALUES ('2018-10-01', '2019-01-01');
INSERT INTO calendar_quarters VALUES ('2019-01-01', '2019-04-01');
INSERT INTO calendar_quarters VALUES ('2019-04-01', '2019-07-01');
INSERT INTO calendar_quarters VALUES ('2019-07-01', '2019-10-01');
INSERT INTO calendar_quarters VALUES ('2019-10-01', '2020-01-01');
INSERT INTO calendar_quarters VALUES ('2020-01-01', '2020-04-01');
INSERT INTO calendar_quarters VALUES ('2020-04-01', '2020-07-01');
INSERT INTO calendar_quarters VALUES ('2020-07-01', '2020-10-01');
INSERT INTO calendar_quarters VALUES ('2020-10-01', '2021-01-01');

Once you have your map of intervals, then you just need to see which intervals overlap with your data...

SELECT
  calendar_quarters.interval_start
FROM
  your_table
INNER JOIN
  calendar_quarters
    ON  your_table.BEGINN_DATE < calendar_quarters.interval_cease
    AND your_table.END_DATE    > calendar_quarters.interval_start
GROUP BY
  calendar_quarters.interval_start

Notice also that the end date of one interval is the same as the start date of the next interval. This uses Inclusive start, exclusive end, is a standard approach, and makes many aspects of date maths/manipulations much easier (search the web for more information).

This means that you may want to add one day to your END_DATE values, add one day to them in the queries, or use >= and <= in the query.

I strongly recommend using exclusive end dates, it really helps a lot.