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

On 02/22/2018 04:58 PM, Ken Tanzer wrote:
>
>
> On Thu, Feb 22, 2018 at 4:53 PM, Adrian Klaver
> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
>
> On 02/22/2018 04:44 PM, Ken Tanzer wrote:
>
> Hi, hoping to get some help with this.  I'm needing to take a
> specific date, a series of dateranges  and, given a specific
> date, return a single conitinuous daterange that includes that date.
>
> To elaborate a bit, I've got lots of tables that include start
> and end dates.  For example:
>
> CREATE TABLE tbl_staff_assign (
>      staff_assign_id         SERIAL PRIMARY KEY,
>      client_id               INTEGER NOT NULL REFERENCES
> tbl_client (client_id),
>      staff_id                INTEGER REFERENCES
> tbl_staff(staff_id),
>      staff_assign_type_code      VARCHAR(10) NOT NULL
> REFERENCES tbl_l_staff_assign_type (staff_assign_type_code),
>      staff_assign_date           DATE NOT NULL,
>      staff_assign_date_end       DATE,
> ...
>
> So a client might leave a progrma and then return later, or they
> might simply switch to another staff_id.  (In which case one
> record will have and end date, and the next record will start on
> the next day.)  In this case I need to know "what period were
> they continuously in the program that includes X date?"  So I'd
> like to be able to do something like:
>
> "SELECT staff_assign_date,continuous_daterange(
> staff_assign_date, (SELECT
> array_agg(daterange(staff_assign_date,staff_assign_date_end,'[]')
> ) FROM staff_assign sa2 WHERE sa2.client_id=sa.client_id) FROM
> staff_assign sa
>
> I've done this before with procedures specific to a particular
> table, and working with the start and end dates.  I'm now
> wanting to try to do this once generically that will work for
> all my cases.  So I'm hoping to do this in a way that
> performance isn't horrible.  And it's a little unclear to me how
> much and how I might be able to use the daterange operators to
> accomplish this efficiently.
>
>
> The operator I use to solve similar problems:
>
> https://www.postgresql.org/docs/10/static/functions-range.html
> <https://www.postgresql.org/docs/10/static/functions-range.html>
>
> @>      contains element        '[2011-01-01,2011-03-01)'::tsrange
> @> '2011-01-10'::timestamp   t
>
>
> Thanks Adrian.  But how would you apply that to this situation, where I
> have a series of (quite possibly discontinuous) dateranges?

This is going to depend on a more formal definition of the problem with
some sample data. Right now I am trying to reconcile "what period were
they continuously in the program that includes X date?" with "where I
have a series of (quite possibly discontinuous) dateranges? " Maybe its
just me, I can't see how discontinuous can also be continuously.

>
>
> --
> AGENCY Software
> A Free Software data system
> By and for non-profits
> /http://agency-software.org//
> /https://demo.agency-software.org/client/
> ken(dot)tanzer(at)agency-software(dot)org <mailto:ken(dot)tanzer(at)agency-software(dot)org>
> (253) 245-3801
>
> Subscribe to the mailing list
> <mailto:agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
> learn more about AGENCY or
> follow the discussion.

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com