Re: Given a set of daterange, finding the continuous range thatincludes a particular date - Mailing list pgsql-general

On 02/22/2018 04:58 PM, Ken Tanzer wrote:
> 
> 
> On Thu, Feb 22, 2018 at 4:53 PM, Adrian Klaver 
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.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.tanzer@agency-software.org <mailto:ken.tanzer@agency-software.org>
> (253) 245-3801
> 
> Subscribe to the mailing list 
> <mailto:agency-general-request@lists.sourceforge.net?body=subscribe> to
> learn more about AGENCY or
> follow the discussion.


-- 
Adrian Klaver
adrian.klaver@aklaver.com