Re: Given a set of daterange, finding the continuous range thatincludes a particular date

Hi.

This answer is perhaps useful if I understand your problem correctly. But I might have interpreted it wrongly. :-)

I would probably start with merging intervals so that overlapping and adjacent intervals become single continuous intervals, then select from those merged intervals.

We have an application with a lot of interval handling in PostgreSQL, and we use many variants of algorithms based on one by Itzik Ben Gan which he calls “packing intervals”. The post we started with was an old reader’s challenge from SQL Magazine. It has since been updated when MS-SQL started supporting window functions better (Itzik Ben Gan is a MS-SQL-guy).

Basically, it is a few CTE:s which convert the intervals into “start” (+1) and “stop” (-1) events, then keeps a running sum of these, and finally creates new intervals by noting that the merged (or “packed”) intervals starts with events that had sum=0 before them and stops with events which have sum=0 after them.

It involves both CTE:s and window functions and is quite a beautiful example of SQL, IMHO.

I think it’s best to google it, he does a good job of explaining how it works.

Hope that helps a bit at least.

/Viktor