Search code examples
sqloracle-databaseoracle11g

Joining two tables based on date values

Hy! I would like to connect the two tables based on date, supplementing table "B" with the CUSTOMID field. I would like to ask for help in implementing the query below. I'm using oracle 11 version.

"A" table looks like...

enter image description here

"B" table looks like...

enter image description here

The expected result should be like this...

enter image description here

Thanks for the answers!

Solution

  • You could union the two tables together, will nulls for the missing columns in each:

    select id, null as name, null as gender, null as age, "DATE", customid from a
union all
select id, name, gender, age, "DATE", null as customid from b
order by "DATE"
    ID NAME GENDER AGE DATE CUSTOMID
    2068121 ST. Oliver 1 18 2011-07-30 05:50 null
    2068121 Km. Oliver 1 18 2012-01-23 11:12 null
    2068121 Km. Oliver 1 18 2021-03-12 17:04 null
    2068121 null null null 2021-03-12 17:06 10729945
    2068121 Km. Oliver 1 18 2022-03-29 11:53 null
    2068121 null null null 2022-07-25 11:01 10729944

    And then fill in the gaps with an analytic query such as last_value to get the last non-null value seen in each column, based on the date order:

    select id,
  last_value(name) ignore nulls over (partition by id order by "DATE") as name,
  last_value(gender) ignore nulls over (partition by id order by "DATE") as gender,
  last_value(age) ignore nulls over (partition by id order by "DATE") as age,
  "DATE",
  last_value(customid) ignore nulls over (partition by id order by "DATE") as customid
from (
  select id, null as name, null as gender, null as age, "DATE", customid from a
  union all
  select id, name, gender, age, "DATE", null as customid from b
)
order by "DATE"

    If you don't want nulls for the first customid values you can coalesce those to zero:

    coalesce(last_value(customid) ignore nulls over (partition by id order by "DATE"), 0) as customid
    ID NAME GENDER AGE DATE CUSTOMID
    2068121 ST. Oliver 1 18 2011-07-30 05:50 0
    2068121 Km. Oliver 1 18 2012-01-23 11:12 0
    2068121 Km. Oliver 1 18 2021-03-12 17:04 0
    2068121 Km. Oliver 1 18 2021-03-12 17:06 10729945
    2068121 Km. Oliver 1 18 2022-03-29 11:53 10729945
    2068121 Km. Oliver 1 18 2022-07-25 11:01 10729944

    db<>fiddle