Skip to content
Advertisement

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:

enter image description here

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:

enter image description here

Many thanks for any pointers…

Advertisement

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:

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

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement