Re: Generate a series of single days from a table of intervals.

On Wed, Jul 27, 2016 at 2:50 PM, Paul Linehan <linehanp(at)tcd(dot)ie> wrote:

> WITH RECURSIVE dates (test_date) AS
> (
> SELECT '2016-03-01'::DATE
> UNION ALL
> SELECT test_date + 1 FROM dates
> )
> SELECT * FROM dates LIMIT 10
>
> I want to JOIN the table above with this table below
>
> CREATE TABLE ranges (datein date, dateout date, status int);
>
> INSERT INTO ranges VALUES ('2016-04-02', '2016-04-05', 1);
> INSERT INTO ranges VALUES ('2016-04-15', '2016-04-18', 1);
> INSERT INTO ranges VALUES ('2016-04-25', '2016-04-30', 1);
>
​​

​WITH dates ()
SELECT test_date, CASE WHEN has_match THEN 1 ELSE 0 END::int AS flag
FROM (
SELECT test_date, EXISTS(SELECT 1 FROM ranges WHERE dates.test_date BETWEEN
ranges.datein AND ranges.dateout) AS has_match
FROM dates
) range_checks;

You can consider something like:

SELECT [...] FROM dates LEFT JOIN ranges ON (test_date BETWEEN datein AND
dateout)

​as well: though it would, at least in part, depend on whether your ranges
are strictly non-overlapping.

​David J.