On Thu, Feb 22, 2018 at 4:53 PM, Adrian Klaver <adrian.kla...@aklaver.com>

> 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/>*
(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.

Reply via email to