Archive for September 9th, 2009
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: