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

On Thu, Feb 22, 2018 at 5:05 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> 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.
>
>
>>
*Start End*
-- Episode 1
1/1/16 3/30/16
4/1/16 4/30/16
-- Episode 2
1/1/18 1/31/18
2/1/18 NULL

Given 1/15/18, should return 1/1/18-->NULL
Given 1/15/16, should return 1/1/16-->4/30/16

Thinking about this a bit more, I'm wondering if a window function could be
used for this? I've used them a bit, but definitely never wrote one.
Something like

continuous_daterange(staff_assign_date,daterange(staff_assign_date,staff_assign_date_end,'[[]')
OVER (PARTION BY client_id)

But then a window function can only see the rows included in the query,
yes? In which case this would only work if you queried for all the rows
for a particular client_id? I guess in the example I gave I was doing that
anyway, so maybe this would be no worse.

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.