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.tan...@agency-software.org (253) 245-3801 Subscribe to the mailing list <agency-general-requ...@lists.sourceforge.net?body=subscribe> to learn more about AGENCY or follow the discussion.
