Given a set of daterange, finding the continuous range that includes a particular date

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.

Any advice or suggestions or ways to go about this appreciated. Thanks!

Ken

p.s., Another small wrinkle is these records aren't always perfect, and
ideally I'd allow for an optional fudge factor that would allow small gaps
to be ignored. I could just add that in every query
(start_date+2,end_date-2), but it might be nice to have the function do it,
if it didn't badly hurt performance.

--
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(dot)tanzer(at)agency-software(dot)org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
learn more about AGENCY or
follow the discussion.