PL-SQL query to calculate customers per period from start and stop dates

I have a PL-SQL table with a structure as shown in the example below:

I have customers (customer_number) with insurance cover start and stop dates (cover_start_date and cover_stop_date). I also have dates of accidents for those customers (accident_date). These customers may have more than one row in the table if they have had more than one accident. They may also have no accidents. And they may also have a blank entry for the cover stop date if their cover is ongoing. Sorry I did not design the data format, but I am stuck with it.

I am looking to calculate the number of accidents (num_accidents) and number of customers (num_customers) in a given time period (period_start), and from that the number of accidents-per-customer (which will be easy once I’ve got those two pieces of information).

Any ideas on how to design a PL-SQL function to do this in a simple way? Ideally with the time periods not being fixed to monthly (for example, weekly or fortnightly too)? Ideally I will end up with a table like this shown below:

Many thanks for any pointers…

Answer

You seem to need a list of dates. You can generate one in the query and then use correlated subqueries to calculate the columns you want:

select d.*,
       (select count(distinct customer_id)
        from t
        where t.cover_start_date <= d.dte and
              (t.cover_end_date > d.date + interval '1' month or t.cover_end_date is null)
       ) as num_customers,
       (select count(*)
        from t
        where t.accident_date >= d.dte and
              t.accident_date < d.date + interval '1' month
       ) as accidents,
       (select count(distinct customer_id)
        from t
        where t.accident_date >= d.dte and
              t.accident_date < d.date + interval '1' month
       ) as num_customers_with_accident
from (select date '2020-01-01' as dte from dual union all
      select date '2020-02-01' as dte from dual union all
      . . .
     ) d;

If you want to do arithmetic on the columns, you can use this as a subquery or CTE.