How to generated missing dates between date range that do not exist in the joined table in CockroachDB?
1
vote
2
answers
265
views
I have Table A and Table A which have
Table A
id, start_date, end_date
Table B
id, table_a_id, archive_date
I want to return the dates that are missing in Table B, the dates that are NOT in the range of table_a start_date and end_date
How can I do this in cockroachdb?
I have tried using generate_series but I get a unknown signature error, generate_series does not take (date, date, interval)
WITH date_series AS (
SELECT generate_series(
(SELECT w.export_start_date FROM table_a w WHERE w.id = 123),
(SELECT w.export_end_date FROM table_a w WHERE w.id = 123),
'1 day'::interval) AS missing_date
)
SELECT ds.missing_date
FROM date_series ds
LEFT JOIN table_b a ON a.table_a_id = 123
Asked by Brunaldo
(23 rep)
Mar 6, 2024, 05:32 PM
Last activity: Mar 12, 2024, 05:02 PM
Last activity: Mar 12, 2024, 05:02 PM