I have two tables:
dates:
date |
---|
2024-01-01 |
2024-01-02 |
2024-01-03 |
2024-01-04 |
2024-01-05 |
actual:
business_date | id |
---|---|
2024-01-01 | 1 |
2024-01-02 | 2 |
2024-01-04 | 4 |
2024-01-05 | 5 |
I want to join these tables on the condition date = business_date, but in the case where there's no match on dates it would get the record of the previous date. So resulting table would look like:
business_date | id | date |
---|---|---|
2024-01-01 | 1 | 2024-01-01 |
2024-01-02 | 2 | 2024-01-02 |
2024-01-02 | 2 | 2024-01-03 |
2024-01-04 | 4 | 2024-01-04 |
2024-01-05 | 5 | 2024-01-05 |
How would I achieve this?
SELECT
*
FROM
dates
CROSS JOIN LATERAL
(
SELECT
*
FROM
actual
WHERE
actual.business_date <= dates.date
ORDER BY
actual.business_date DESC
LIMIT
1
)
AS actual
Or...
WITH
actual_ranged AS
(
SELECT
*,
LEAD(business_date, 1, 'infinity')
OVER (
ORDER BY business_date
)
AS next_business_date
FROM
actual
)
SELECT
*
FROM
actual_ranged AS a
LEFT JOIN
dates AS d
ON d.date >= a.business_date
AND d.date < a.next_business_date
Demo : https://dbfiddle.uk/tzjjhZJu