sqldatabasepostgresqlwindow-functionsgaps-and-islands

How to get previous record by date in case of no match in a join condition


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?


Solution

  • 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