Re: Generate a series of single days from a table of intervals.
| От | David G. Johnston |
|---|---|
| Тема | Re: Generate a series of single days from a table of intervals. |
| Дата | |
| Msg-id | CAKFQuwbgYJuw0RUA7X266-xSF8NKTv6GXVwue-Eq3NF8W63zhA@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Generate a series of single days from a table of intervals. (Paul Linehan <linehanp@tcd.ie>) |
| Ответы |
Re: Generate a series of single days from a table of intervals.
|
| Список | pgsql-novice |
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);
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;
You can consider something like:
SELECT [...] FROM dates LEFT JOIN ranges ON (test_date BETWEEN datein AND dateout)
as well: though it would, at least in part, depend on whether your ranges are strictly non-overlapping.
David J.
В списке pgsql-novice по дате отправления: