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...
"B" table looks like...
The expected result should be like this...
Thanks for the answers!
CodePudding user response：
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 |
CodePudding user response：
Hm, how do you plan to join those "dates" if they don't match? By year (that's what does match)?
Anyway, see if this helps.
Sample data:
SQL> with
2 a (id, customid, datum) as
3 (select 2068121, 107299445, to_date('2021.03.12 17:06', 'yyyy.mm.dd hh24:mi') from dual union all
4 select 2068121, 107299444, to_date('2022.07.25 11:01', 'yyyy.mm.dd hh24:mi') from dual
5 ),
6 b (id, name, gender, age, datum) as
7 (select 2068121, 'St. Oliver', 1, 18, to_date('2011.07.30 05.50', 'yyyy.mm.dd hh24:mi') from dual union all
8 select 2068121, 'Km. Oliver', 1, 18, to_date('2012.01.23 11.12', 'yyyy.mm.dd hh24:mi') from dual union all
9 select 2068121, 'Km. Oliver', 1, 18, to_date('2021.03.12 17:04', 'yyyy.mm.dd hh24:mi') from dual union all
10 select 2068121, 'Km. Oliver', 1, 18, to_date('2022.03.29 11:53', 'yyyy.mm.dd hh24:mi') from dual
11 )
Query begins here: first select what you already have (contents of table b):
12 select b.id, b.name, b.gender, b.age, b.datum, 0 customid from b
Then add what's missing - joined a and b tables on ID and years from the DATUM column:
13 union all
14 select a.id, b.name, b.gender, b.age, a.datum, a.customid
15 from a join b on a.id = b.id and trunc(a.datum, 'yyyy') = trunc(b.datum, 'yyyy')
16 order by id, name desc, gender, datum;
ID NAME GENDER AGE DATUM 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 107299445
2068121 Km. Oliver 1 18 2022.03.29 11:53 0
2068121 Km. Oliver 1 18 2022.07.25 11:01 107299444
6 rows selected.
SQL>