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

Hi all, I've managed to make some progress on my RECURSIVE
CTE.

However, I can't for the life of me figure out how to join the table
dates produced by the WITH RECURSIVE query below with
the ranges table from the DDL and DML given previously (also below),


Ultimately, I need to get the result (also given below) - with status = 0 or 1
depending on whether the dates produced by the RECURSIVE
query are in the intervals in the  ranges table. I can use
more CTEs to try and do this - is only 1 WITH RECURSIVE query
allowed per overall query - you can have multiple CTEs per
query.

Any pointers appreciated.



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);

to get this result - again, any help greatly appreciated.

 date_val       status

2016-04-01       0
2016-04-02       1
2016-04-03       1
2016-04-04       1
2016-04-05       1
2016-04-06       0
..
..
..<status as appropriate for the rest of the month>
..
..
2016-04-30       1


TIA and rgs,


Paul...


> David J.