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

Hi all,


I have a table of date intervals (see below for DDL and DML) with a
status of 1 for those periods. Dates outside of these intervals
need a status of 0.

I wish to generate a series of dates from these intervals and
also to generate the intervening dates with a status of 0 (say
in this example for the month of April - the outying date parameters
should be arbitrary - maybe 1 month, maybe three.

So, take the example first record (of the three sample records) below,

The output I need is:

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
2016-04-02       0
..
..<status as appropriate for the rest of the month>
..
2016-04-30       0


I would like to do this using a recursive CTE if it's the best solution,
but I'm open to other approaches - different approaches appreciated.


Please let me know if you require any further information.


TIA and rgs,


Paul...



CREATE TABLE testdate (datein date, dateout date, status int);

INSERT  INTO testdate VALUES ('2016-04-02', '2016-04-05', 1);
INSERT  INTO testdate VALUES ('2016-04-15', '2016-04-18', 1);
INSERT  INTO testdate VALUES ('2016-04-25', '2016-04-30', 1);

Hi, and thanks for your quick reply,


> https://www.postgresql.org/docs/9.5/static/functions-srf.html


I know about generate_series, but I would like this to
be generally applicable.


> See "LEFT JOIN" and "COALESCE"
>

Will do.

>> 2016-04-02       0

> Typo?

Yes :-)


>> I would like to do this using a recursive CTE if it's the best solution,
>> but I'm open to other approaches - different approaches appreciated.


> Really?  That should be the last choice of execution strategy.

> This isn't a tree, I'm doubtful that a recursive CTE is needed.

But recursive CTEs can be used to generate series, and as I
said, I don't want to use a PostgreSQL specific function.

> https://www.postgresql.org/docs/9.5/static/functions-range.html

Will take a look, thanks again,


Paul...


> David J.

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.

Hi,

typo - that should be LIMIT 30 in the WITH RECURSIVE query, not 10.
Apologies for any inconvenience.

P...

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

Hi again all,


> March...
> April...

Doh <slaps forehead>

What a muppet! It was in fact an issue of dates not actually
matching!

Apologies to David Johnston for doubting your logic, both of your
queries worked.

And thanks yet again for your help - it was much appreciated


P...


> David J.