Hi all, consider the following data:
with data1 as
(
select 123 pid, 222 pdb, to_date('01/01/2022', 'mm/dd/yyyy') dt, 456 balance, 42.12 chg, 1 stm from dual union all
select 123 pid, 222 pdb, to_date('01/01/2022', 'mm/dd/yyyy') dt, 456 balance, 45.12 chg, 1 stm from dual union all
select 234 pid, 333 pdb, to_date('01/04/2022', 'mm/dd/yyyy') dt, 56 balance, 4.12 chg, 1 stm from dual union all
select 234 pid, 333 pdb, to_date('01/04/2022', 'mm/dd/yyyy') dt, 56 balance, 1.12 chg, 1 stm from dual union all
select 555 pid, 444 pdb, to_date('01/05/2022', 'mm/dd/yyyy') dt, 56 balance, 1.12 chg, 1 stm from dual

)
,data2 as
(
select 123 pid, 222 pdb, to_date('01/01/2022', 'mm/dd/yyyy') dt, 456 balance, 42.12 chg, 1 stm from dual union all
select 123 pid, 222 pdb, to_date('01/01/2022', 'mm/dd/yyyy') dt, 456 balance, 45.12 chg, 1 stm from dual union all

select 123 pid, 222 pdb, to_date('01/02/2022', 'mm/dd/yyyy') dt, 777 balance, 12.12 chg, 1 stm from dual union all
select 123 pid, 222 pdb, to_date('01/02/2022', 'mm/dd/yyyy') dt, 777 balance, 15.12 chg, 1 stm from dual union all

select 234 pid, 333 pdb, to_date('01/04/2022', 'mm/dd/yyyy') dt, 56 balance, 4.12 chg, 1 stm from dual union all
select 234 pid, 333 pdb, to_date('01/04/2022', 'mm/dd/yyyy') dt, 56 balance, 4.12 chg, 1 stm from dual
)
I would like to display the following output:
pid pdb dt balance chg stm previous_bal

123 222 01/01/2022 777 42.12 1 456
123 222 01/01/2022 777 45.12 1 456
123 222 01/02/2022 777 12.12 1 456
123 222 01/02/2022 777 15.12 1 456

234 333 01/04/2022 56 4.12 2 56
234 333 01/04/2022 56 1.12 2 56

the way i got this output is as follow: data2 dataset is the latest data and should be used as the base table. data1 is existing data. I want to join data2 to data1 and I want to check if data in data2 table exists in data1 table base on pid, padb and dt column. the logic goes like this: if data in data2 exists in data1 (any number of rows) then output the data in data2 table as is and derive a new column call previous_bal with the preivous balance value from data1 for all rows even new rows in data2. if data2 contains the same number of rows in data1 and there is not new rows in data2 , then output the data in data2 as is and populate previous_bal and change the stm column from 1 to 2.
for example, pid =123 and pdb =222. there are two rows in data1 dataset but 4 rows in data2 dataset. the rows with dt 1/1/2022 in data2 matches with data1 but data2 has two new rows with dt 1/2/2022. in this case some rows match and therefore the data in data2 should be output as is and previous_bal value should contain the value of the two rows matched in data1 for all rows output.
for pid=234, there is no new rows in data2. everything matches to data1 so the data in data2 should be output as is but stm column should change to 2 and previous bal should be populated with same value as bal.
for pid=555 in data1, there is no such rows in data2 and therefore nothing should be display since the data2 table is the base table and output should be display from there.
i am using oracle 11g. i was thinking of using exists and not exists but it doesnt take care of what i want. then was thinking of using lag function but didnt know how to get the output as mentioned above.
can someone help me write a query that generate the output above? im using oracle 11g
thanks in advance