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