making records active nonactive from stage tables

i have data like below:
day 1
stg_employee
empllid  flag    row_number week  dateloaded
1111       a          2                      1             20180101
1111      d            4                    2             20180101
1111     a             4                    3             20180101

day 2

empllid  flag    row_number week  dateloaded
1111      d            10                  2             20180202

i just want to data with flag a and have date expiry based upon r0w_number

dim_employee day 1

empllid  flag    row_number week  dateloaded       date_expiry
1111       a          2                      1             20180101     20180101
1111     a             4                    3             20180101       null(means active record)


day 2

empllid  flag    row_number week  dateloaded       date_expiry
1111       a          2                      1             20180101     20180101
1111     a             4                    3             20180101       20180202 ---as day 2 we get record d which we don't want to insert but date expiry should change from null to day2
sam2929Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SujithData ArchitectCommented:
Your requirements are not complete.

what if the second day you receive an a with Row number 4?
what if the second day you receive an a with row number 5?
0
sam2929Author Commented:
Correct for second day it might be 1 but if we receive flag d we should expire day 1 record
0
Mark GeerlingsDatabase AdministratorCommented:
We don't have enough information from you to understand the problem you are trying to solve.

1. Do you have two tables: stg_employee and dim_employee?
2. How do the "row_number" and "week" values from the stg_employee table get used?  In your sample data here, they don't seem to match the values in the dim_employee table records.  Or, don't those column values matter?
3. I don't understand what you mean by this: "i just want to data with flag a and have date expiry based upon r0w_number".
    a. What do you want to do with records "with flag a"?  Are those records in stg_employee only?
    b. How is date expiry related to, or determined, from row_number?
4. This is not clear either: "as day 2 we get record d which we don't want to insert but date expiry should change from null to day2".
   a. "we don't want to insert"  Insert what?  Into which table?
   b. "date expiry should change from null to day2"  This change must be in the dim_employee table, correct?  Does this table have multiple records for each empllid, or only one?  And that change is based on the existence of a matching record in the stg_employee table?   Is that match based only on empllid, or also on "row number" and/or "week"?
0
SujithData ArchitectCommented:
You can do this with a conditional merge statement (but with a lot of caveats and unknown in what you have stated here)

see an illustration

SQL> drop table stg_employee;

Table dropped.

SQL>
SQL> create table stg_employee(empllid  varchar2(20), flag    varchar2(20),
  2  row_number varchar2(20), week  varchar2(20), dateloaded varchar2(20));

Table created.

SQL>
SQL> begin
  2  insert into stg_employee values('1111', 'a',         2,                    1,             20180101);
  3  insert into stg_employee values('1111', 'd',         4,                    2,             20180101);
  4  insert into stg_employee values('1111', 'a',         4,                    3,             20180101);
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> drop table dim_employee;

Table dropped.

SQL>
SQL> create table dim_employee (empllid  varchar2(20), flag    varchar2(20)
  2  , row_number varchar2(20), week  varchar2(20), dateloaded varchar2(20), date_expiry varchar(20));

Table created.

SQL>
SQL> merge   into dim_employee dim
  2  using(
  3      select  empllid, flag, row_number, week, dateloaded, case when rn = 1 and flag = 'a' then null else mdt end mdt
  4      from (
  5      select  empllid, flag, row_number, week, dateloaded,
  6      row_number() over (partition by empllid||flag order by row_number desc ) rn, max(dateloaded) over() mdt
  7      from
  8      (
  9      select  *
 10      from    stg_employee
 11      --where   flag    = 'a'
 12      )
 13      )
 14  ) stg
 15  on(
 16      dim.empllid = stg.empllid
 17  )
 18  when matched then
 19  update
 20      set dim.date_expiry = case when dim.date_expiry is null then stg.dateloaded else dim.date_expiry end
 21  when not matched then
 22  insert  (empllid, flag, row_number, week, dateloaded, date_expiry)
 23  values  (stg.empllid, stg.flag, stg.row_number, stg.week, stg.dateloaded, stg.mdt)
 24  where   stg.flag = 'a'
 25  ;

2 rows merged.

SQL>
SQL> select  * from dim_employee;

EMPLLID              FLAG                 ROW_NUMBER           WEEK                 DATELOADED        DATE_EXPIRY
-------------------- -------------------- -------------------- -------------------- -------------------- --------------------
1111                 a                    2                    1                    20180101          20180101
1111                 a                    4                    3                    20180101

SQL>
SQL> delete from stg_employee;

3 rows deleted.

SQL>
SQL> insert into stg_employee values('1111', 'd', 10, 2, 20180202);

1 row created.

SQL>
SQL> merge   into dim_employee dim
  2  using(
  3      select  empllid, flag, row_number, week, dateloaded, case when rn = 1 and flag = 'a' then null else mdt end mdt
  4      from (
  5      select  empllid, flag, row_number, week, dateloaded
  6      , row_number() over (partition by empllid||flag order by row_number desc ) rn, max(dateloaded) over() mdt
  7      from
  8      (
  9      select  *
 10      from    stg_employee
 11      --where   flag    = 'a'
 12      )
 13      )
 14  ) stg
 15  on(
 16      dim.empllid = stg.empllid
 17  )
 18  when matched then
 19  update
 20      set dim.date_expiry = case when dim.date_expiry is null then stg.dateloaded else dim.date_expiry end
 21  when not matched then
 22  insert  (empllid, flag, row_number, week, dateloaded, date_expiry)
 23  values  (stg.empllid, stg.flag, stg.row_number, stg.week, stg.dateloaded, stg.mdt)
 24  where   stg.flag = 'a'
 25  ;

2 rows merged.

SQL>
SQL> select * from dim_employee;

EMPLLID              FLAG                 ROW_NUMBER           WEEK                 DATELOADED        DATE_EXPIRY
-------------------- -------------------- -------------------- -------------------- -------------------- --------------------
1111                 a                    2                    1                    20180101          20180101
1111                 a                    4                    3                    20180101          20180202

SQL>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.