If I have a postgres table a:
member | start | end
---------+------------+------------
1 | 2015-01-01 | 2015-05-01
---------+------------+------------
1 | 2015-03-01 | 2015-06-01
---------+------------+------------
2 | 2015-01-01 | 2015-05-01
---------+------------+------------
2 | 2015-06-01 | 2015-08-01
How would I coalesce dates to eliminate overlapping ranges like this:
member | start | end
---------+------------+------------
1 | 2015-01-01 | 2015-06-01
---------+------------+------------
2 | 2015-01-01 | 2015-05-01
---------+------------+------------
2 | 2015-06-01 | 2015-08-01
In the
chop
CTE original ranges are "chopped" into smaller, non-intersecting (but possibly adjacent) ranges. They are constructed from all the end points of the original ranges, both start and finish.Main select works as follows (read it from the inside out):
Black magic of window functions...
I renamed
end
tofinish
becauseend
is a keyword.