CODEWITHSUNDEEP
sqlpostgresqlamazon-redshift
user9621577
user9621577

Reputation: 51

SQL multiple date ranges

I need to fetch data from a table where the date is in one of several date ranges. These date ranges are in another table and the number of date ranges varies.

The date range table looks like this:

+----------+------------+------------+
| variable | start_date |  end_date  |
+----------+------------+------------+
| A        | 2017-01-01 | 2017-02-05 |
| A        | 2018-07-04 | 2017-09-09 |
| A        | 2019-08-08 |            |
| B        | 2011-10-10 | 2012-02-05 |
+----------+------------+------------+

and the table that I want to filter looks like:

+----------+------------+--------------+
| variable |    date    | other_values |
+----------+------------+--------------+
| A        | 2017-01-04 |          123 |
| A        | 2016-07-04 |          123 |
| B        | 2020-01-01 |              |
| C        | 2018-02-07 |          123 |
+----------+------------+--------------+

I need a query the gets all rows from the second table where the date is in one of the date ranges of the first table. If the end_date is Null, the date_range goes from the start_date to infinity.

For each variable I might have a different number of rows in the date range table.

For this example, my output should be:

+----------+------------+--------------+
| variable |    date    | other_values |
+----------+------------+--------------+
| A        | 2017-01-04 |          123 |
| B        | 2020-01-01 |              |
+----------+------------+--------------+

Thanks for your help.

Upvotes: 0

Views: 1092

Answers (2)

user330315
user330315

Reputation:

You can use a range type in Postgres:

select t2.*
from table_2 t2
where exists (select *
              from table_1 t1
              where t1.variable = t2.variable
                and t1.date <@ daterange(t1.start_date, t1.end_date, '[]'));

A date range treats null as an open end.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269773

One method uses exists:

select t2.*
from t2
where exists (select 1
              from table1 t1
              where t1.variable = t2.variable and
                    t2.date >= t1.start_date and
                    (t2.date < t1.end_date or t1.end_date is null)
             );

You don't have overlapping ranges in the first table, so you can also use a join without worrying about duplicates:

select t2.*
from t2 join
      t1
      on t1.variable = t2.variable and
         t2.date >= t1.start_date and
         (t2.date < t1.end_date or t1.end_date is null);

Upvotes: 1

Related Questions