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

> On 02/22/2018 04:58 PM, Ken Tanzer wrote:
>> On Thu, Feb 22, 2018 at 4:53 PM, Adrian Klaver <adrian.kla...@aklaver.com
>> <mailto:adrian.kla...@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.
*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

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.

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