Re: Given a set of daterange, finding the continuous range that includes a particular date (aggregates)

Hi. Thanks so much for your assistance. This is definitely getting the
results I was looking for. It is still syntacticallly more cumbersome than
I might have hoped, but I can work with it. So I've got two follow
questions/issues:

1) I can see there are many, more complex, options for aggregates, which I
am trying to wrap my mind around. I'm wondering if any of these (esp.
partial aggregates/combine functions, final functions or moving aggregates)
could be used to streamline this into a single function call, or if that is
barking up a dead tree.

2) I'm sure at this point I must be being dense, but after re-reading docs
multiple times, I am still confused about one piece of this:

> first window (within lower() func) extends its initial (earliest) range
> > to right for every next row continuous with current range (and jumps to
> > new start on discontinuity), thus over(order by d ASC)
> > second window (within upper() func) extends its initial (latest) range
> > to left for every next row continuous with current range (and jumps to
> > new end on discontinuity), thus over(order by d DESC)
> > partition by client_id within each over() is to not mix client_ids'
> > dateranges
> >
>
> which is what is it exactly that is triggering Postgresql to know there is
a discontinuity and to start with a new range? And is it based on the
input or the output values? Based on PARTITION BY client_id ORDER BY d, I
might have thought it was d. But that doesn't seem to be right. So is it
something about what agg_daterange is returning, and if so what? Again,
sorry for being dense about this one.

Thanks!

Ken

--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/>*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client>*
ken(dot)tanzer(at)agency-software(dot)org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
learn more about AGENCY or
follow the discussion.