On Thu, Feb 22, 2018 at 4:53 PM, Adrian Klaver <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 > > @> 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/>* *https://demo.agency-software.org/client <https://demo.agency-software.org/client>* ken.tan...@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.
