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.
