Re: Generate a series of single days from a table of intervals. - Mailing list pgsql-novice

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