CODEWITHSUNDEEP
sqldateamazon-redshift
user2022284
user2022284

Reputation: 443

Joining two tables by date ranges

I have two tables I am looking to join by their dates:

table 1: reference

| date           |
| -------------- |
| 2018-01-01     |
| 2018-01-02     |
| 2018-01-03     |
| 2018-01-04     |
| 2018-01-05     |
| 2018-01-06     |
| 2018-01-07     |
| 2018-01-08     |
| 2018-01-09     |
| 2018-01-10     |

table 2: events

| id | date       |
| -- | ---------- |
| aa | 2018-01-03 |
| bb | 2018-01-05 |
| cc | 2018-01-08 |

I am looking to join the two by their date fields to achieve the following results:

| id | reference date | event date |
| -- | -------------- | ---------- |
| aa | 2018-01-03     | 2018-01-03 |
| aa | 2018-01-04     | 2018-01-03 |
| bb | 2018-01-05     | 2018-01-05 |
| bb | 2018-01-06     | 2018-01-05 |
| bb | 2018-01-07     | 2018-01-05 |
| cc | 2018-01-08     | 2018-01-08 |
| cc | 2018-01-09     | 2018-01-08 |
| cc | 2018-01-10     | 2018-01-08 |

i.e for each event id, the reference date starts at the earliest event date for that id and ends when the next id kicks in. ( I hope i explained it well).

the code below works if I only have one event with one date. but with multiple events and start dates, it falls apart.

select
  events.id,
  reference.date,
  events.date
from events 
join reference on
  reference.date >= events.date

Upvotes: 0

Views: 2395

Answers (2)

Salman Arshad
Salman Arshad

Reputation: 272106

If you have access to window functions and CTEs you can simply use LEAD:

WITH cte AS (
    SELECT id, date, LEAD(date, 1) OVER (ORDER BY date) AS end_date
    FROM events
)
SELECT cte.id, reference.date AS reference_date, cte.date AS event_date
FROM cte
INNER JOIN reference ON reference.date >= cte.date
    AND (reference.date < cte.end_date OR cte.end_date IS NULL)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269803

Use a correlated subquery:

select r.*,
       (select e.id
        from events e
        where e.date <= r.date
        order by e.date desc
        fetch first 1 row only
       ) as event_date,
       (select e.date
        from events e
        where e.date <= r.date
        order by e.date desc
        fetch first 1 row only
       ) as event_date
from reference e;

Note that this uses ANSI-standard syntax. The syntax for returning a single row could vary by database.

Many databases also support lateral joins and/or cross apply which simplifies the subqueries.

Upvotes: 0

Related Questions