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

Hi again, and I really appreciate your assistance,

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

This is what I tried.

<other CTEs above this>
,
x AS
(
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
)
SELECT * FROM x
LIMIT 30;

The result is a list of dates for the entire month with 0 for all statuses.

It doesn't pick up the ranges - I can't help feeling that it is necessary to
"expand" the ranges into a list of dates for a query to match or not the lists?
of dates rather than compare a date to a range.

I also ran

z AS
(
SELECT d.test_date, r.datein, r.dateout FROM dates d
LEFT JOIN ranges r ON
d.test_date BETWEEN r.datein and r.dateout
)
SELECT * FROM z
LIMIT 30;

And that produces nothing but a list of test_dates
for the month with nothing in the r.datein or .dateout
columns. I think this is for the same reasons.

Any thoughts appreciated.

Rgs,

P...