EXPLAIN EXTENDED

How to create fast database queries

Archive for September 9th, 2009

Oracle: generating a list of dates and counting ranges for each date

with 10 comments

From Stack Overflow:

I have a table with data such as below:

Group Start Date End Date
A 2001.01.01 2001.01.03
A 2001.01.01 2001.01.02
A 2001.01.03 2001.01.03
B 2001.01.01 2001.01.01

I am looking to produce a view that gives a count for each day

We have a list of ranges here, and for each date we should count the number of ranges that contain this date.

To make this query, we will employ one simple fact: the number of ranges containing a given date is the number of ranges started on or before this date minus the number of ranges that ended before this date.

This can easily be calculated using window functions.

Let's create a sample table:

Read the rest of this entry »

Written by Quassnoi

September 9th, 2009 at 11:00 pm

Posted in Oracle