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

On 02/22/2018 09:05 PM, Adrian Klaver wrote:
> On 02/22/2018 05:23 PM, Ken Tanzer wrote:
>>
>>
>> On Thu, Feb 22, 2018 at 5:05 PM, Adrian Klaver 
>> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>>
>>     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>
>>         <mailto: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>
>>         <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
> 
> Just thinking out loud here, in a function:
> 
> 1) For a client_id you can find the min(staff_assign_date).
> 
> 2) You can create a max(staff_assign_date_end) by using 
> COALESCE(staff_assign_date_end, current_date)
> 
> 3) You now have the outer range for the episodes.
> 
> 4) In that range of dates for each staff_assign_date you find the 
> staff_assign_date_end that greater then the staff_assign_date but less 
> then the next staff_assign_date. That will give you your episodes.
> 
> 5) You can then test to see if the X date is in the calculated episodes.
> 
> 6) As a shortcut you can find min(staff_assign_date) that is less then 
> the X date and see if the staff_assign_date_end is greater then the X date.
> 

Correction to 6)

6) As a shortcut you can find max(staff_assign_date) that is less then 
the X date and see if the staff_assign_date_end is greater then the X date.


-- 
Adrian Klaver
adrian.klaver@aklaver.com