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 business_dates(business_date date,id int);
insert into business_dates 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


with cte as (select business_date, coalesce(a.date,b.business_date) date, b.id 
             from dates a full outer join business_dates b 
                     on a.date=b.business_date)
select coalesce(business_date,lag(business_date)over w1) as business_date, 
       coalesce(id,lag(id)over w1) as id, 
       date
from cte
window w1 as (order by date)
order by date;


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

null
null
2024-01-04

2024-01-05
4
2024-01-05

2024-01-06
5
2024-01-06



SELECT 6


with cte as (select business_date as bd, coalesce(a.date,b.business_date) d, b.id 
             from dates a full outer join business_dates b 
                     on a.date=b.business_date)
select (array_agg(bd)filter(where bd is not null)over w1)[1] as business_date, 
       (array_agg(id)filter(where id is not null)over w1)[1] as id, 
       d as date
from cte
window w1 as (order by d
              desc rows between current row and unbounded following)
order by date;


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-02
2
2024-01-04

2024-01-05
4
2024-01-05

2024-01-06
5
2024-01-06



SELECT 6

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
null	null	2024-01-04
2024-01-05	4	2024-01-05
2024-01-06	5	2024-01-06