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

On Thu, Feb 22, 2018 at 4:53 PM, Adrian Klaver <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
>
> @> 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?

--
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.