Join one row with last foregoing date in Vertica

Question

I want to get the status in table B that was current on the date of an event in table A.

Table A
Person: Date:       Event
Bob     2020-01-05  Party

Table B
Person:  Date: Status
Bob      2020-01-08     Awake 
Bob      2020-01-03     Dinner
Bob      2020-01-01     Cycling

So the right result will be

Bob     2020-01-05  Party Dinner

I tried this but got error 'Correlated subquery expression without aggregates and with limit is not supported'.

SELECT 
    A.Person, 
    A.Date, 
    A.Event,
    (
        SELECT Status FROM B
        WHERE A.Person = B.Person AND B.Date <= A.Date
        ORDER BY B.Date desc
        LIMIT 1
    ) AS Status
FROM A

Can anyone tell me what I should do instead? I tried a few other ideas but keep running into other errors.

Answer 1

Vertica has the "Event Series Join" !

left, right or full outer join two tables with the INTERPOLATE PREVIOUS value predicate, which joins the current row with the immediately preceding row of the other table, according to the join column:

WITH 
a(person, dt,Event) AS (
SELECT 'Bob',DATE '2020-01-05','Party'
)
,
b(person,dt,status) AS (
          SELECT 'Bob', DATE '2020-01-08','Awake'
UNION ALL SELECT 'Bob', DATE '2020-01-03','Dinner'
UNION ALL SELECT 'Bob', DATE '2020-01-01','Cycling'
)
SELECT
  a.person
, a.dt
, a.event
, b.status
FROM a
LEFT OUTER JOIN b
 ON a.person = b.person
AND a.dt INTERPOLATE PREVIOUS VALUE b.dt
;
-- out  person |     dt     | event | status 
-- out --------+------------+-------+--------
-- out  Bob    | 2020-01-05 | Party | Dinner