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.


The part about joining multiple touching dateranges to give a single continuous daterange is what Richard Snodgrass calls "coalescing" in *Developing Time-Oriented Database Applications in SQL*, pages 159 - 169, available printed or as a free PDF at http://www2.cs.arizona.edu/~rts/publications.html (His approach also supports overlapping ranges, but it sounds like you don't need that.) If you had a coalesced view (or maybe a set-returning function), you could do this:

SELECT  term
FROM    coalesced_staff_assign
WHERE   client_id = 5
AND     term @> '2018-15-01'
;

I can't think of any way to avoid scanning all of a given client's records, but hopefully client_id alone would be selective enough to still give you good performance.

Oh also: in reading Snodgrass's SQL, note that he assumes closed-open ranges (i.e. '[)'), so you'll need to adjust some things to fit with your closed-closed ranges (or always use `staff_assign_date_end - INTERVAL '1 day'` if every assignment is at least 1 day long). On the other hand with built-in range types you might be able to simplify his pure-SQL solutions.

--
Paul              ~{:-)
p...@illuminatedcomputing.com

Reply via email to