Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table dates(date date);
insert into dates values
('2024-01-01'),
('2024-01-02'),
('2024-01-03'),
('2024-01-04'),
('2024-01-05'),
('2024-01-06')
  returning *;
--actual:
create table actual(business_date date,id int);
insert into actual values
('2024-01-01',	1),
('2024-01-02',	2),
('2024-01-05',	4),
('2024-01-06',	5)
returning *;
--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 --here
2024-01-04	4	2024-01-04
2024-01-05	5	2024-01-05
*/

CREATE TABLE




date


2024-01-01

2024-01-02

2024-01-03

2024-01-04

2024-01-05

2024-01-06



INSERT 0 6



CREATE TABLE




business_date
id


2024-01-01
1

2024-01-02
2

2024-01-05
4

2024-01-06
5



INSERT 0 4


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


date
business_date
id


2024-01-01
2024-01-01
1

2024-01-02
2024-01-02
2

2024-01-03
2024-01-02
2

2024-01-04
2024-01-02
2

2024-01-05
2024-01-05
4

2024-01-06
2024-01-06
5



SELECT 6


WITH
  actual_ranged AS
(
  SELECT
    *,
    LEAD(business_date, 1, '9999-12-31')
      OVER (
        ORDER BY business_date
      )
        AS next_business_date
  FROM
    actual
)
SELECT
  *
FROM
  actual_ranged AS a
INNER JOIN
  dates         AS d
    ON  d.date >= a.business_date
    AND d.date <  a.next_business_date


business_date
id
next_business_date
date


2024-01-01
1
2024-01-02
2024-01-01

2024-01-02
2
2024-01-05
2024-01-02

2024-01-02
2
2024-01-05
2024-01-03

2024-01-02
2
2024-01-05
2024-01-04

2024-01-05
4
2024-01-06
2024-01-05

2024-01-06
5
9999-12-31
2024-01-06



SELECT 6

date	business_date	id
2024-01-01	2024-01-01	1
2024-01-02	2024-01-02	2
2024-01-03	2024-01-02	2
2024-01-04	2024-01-02	2
2024-01-05	2024-01-05	4
2024-01-06	2024-01-06	5