CODEWITHSUNDEEP
sqloracle-database
user1751356
user1751356

Reputation: 615

oracle analytical function through comparison

I have a table in oracle database with data like below 

G_DATE  P_KEY   P_STATUS    P_USER  P_CD
3/1/2019    1   old           a      bb
3/1/2019    2   old           b      ab
3/1/2019    3   new           c      cb
3/1/2019    4   med           c      cb
3/2/2019    1   old           a      bb
3/2/2019    2   old           b      ab
3/2/2019    3   new           c      cb
3/2/2019    4   med           c      cb
3/3/2019    1   old           a      bb
3/3/2019    2   new           d      ab
3/3/2019    3   med           d      cb
3/3/2019    4   new           c      cb
3/4/2019    1   med           d      bb
3/4/2019    2   old           d      xy
3/4/2019    3   med           d      cb
3/4/2019    5   new           c      cb
3/5/2019    1   old           a      bb
3/5/2019    2   new           d      ab
3/5/2019    3   med           d      cb
3/5/2019    5   new           c      xy

and i am trying to come up with code to select below data using analytical functions but i am struggling to compare the combinations.

it is like we are constructing a slowly changing dimension table with effective and end dates populated whenever there is a change in any of those 3 attributes. p_STATUS, p_cD, P_UsER natural key is g_date and p_key. If you notice the output i posted, we have to track the changes for any change in attribute for a given p_key and g_date.

primary key of the above table is p_key and g_date and a new row needs to be selected for every change in p_status, p_user and p_cd with eff_dt and end_dt accordingly for every combination of a g_date and p_key. Could you please help me with some ideas on how to achieve below output 

P_KEY   P_STATUS    P_USER  P_CD    eff_dt      end_dt  latest_row_flag
1       old         a       bb      3/1/2019    3/3/2019    N
1       med         d       bb      3/4/2019    3/4/2019    N
1       old         a       bb      3/5/2019    12/31/4712  Y
2       old         b       ab      3/1/2019    3/2/2019    N
2       new         d       ab      3/3/2019    3/3/2019    N
2       old         d       xy      3/4/2019    3/5/2019    N
2       new         d       ab      3/5/2019    12/31/4712  Y
3       new         c       cb      3/1/2019    3/2/2019    N
3       med         d       cb      3/3/2019    12/31/4712  Y
4       med         c       cb      3/1/2019    3/2/2019    N
4       new         c       cb      3/3/2019    12/31/4712  Y
5       new         c       cb      3/4/2019    3/4/2019    N
5       new         c       xy      3/5/2019    12/31/4712  Y

---- scripts for creating table and inserting rows into the table

create table work_audit
( g_date date, 
  P_key  number, 
  P_status varchar2(10),
  p_user  varchar2(10),
  p_cd varchar2(10) 
);

SET DEFINE OFF;
Insert into WORK_AUDIT
   (G_DATE, P_KEY, P_STATUS, P_USER, P_CD)
 Values
   (TO_DATE('3/1/2019', 'MM/DD/YYYY'), 1, 'old', 'a', 'bb');
Insert into WORK_AUDIT
   (G_DATE, P_KEY, P_STATUS, P_USER, P_CD)
 Values
   (TO_DATE('3/1/2019', 'MM/DD/YYYY'), 3, 'new', 'c', 'cb');
Insert into WORK_AUDIT
   (G_DATE, P_KEY, P_STATUS, P_USER, P_CD)
 Values
   (TO_DATE('3/1/2019', 'MM/DD/YYYY'), 4, 'med', 'c', 'cb');
Insert into WORK_AUDIT
   (G_DATE, P_KEY, P_STATUS, P_USER, P_CD)
 Values
   (TO_DATE('3/1/2019', 'MM/DD/YYYY'), 2, 'old', 'b', 'ab');
Insert into WORK_AUDIT
   (G_DATE, P_KEY, P_STATUS, P_USER, P_CD)
 Values
   (TO_DATE('3/2/2019', 'MM/DD/YYYY'), 3, 'new', 'c', 'cb');
Insert into WORK_AUDIT
   (G_DATE, P_KEY, P_STATUS, P_USER, P_CD)
 Values
   (TO_DATE('3/2/2019', 'MM/DD/YYYY'), 1, 'old', 'a', 'bb');
Insert into WORK_AUDIT
   (G_DATE, P_KEY, P_STATUS, P_USER, P_CD)
 Values
   (TO_DATE('3/2/2019', 'MM/DD/YYYY'), 2, 'old', 'b', 'ab');
Insert into WORK_AUDIT
   (G_DATE, P_KEY, P_STATUS, P_USER, P_CD)
 Values
   (TO_DATE('3/2/2019', 'MM/DD/YYYY'), 4, 'med', 'c', 'cb');
Insert into WORK_AUDIT
   (G_DATE, P_KEY, P_STATUS, P_USER, P_CD)
 Values
   (TO_DATE('3/3/2019', 'MM/DD/YYYY'), 1, 'old', 'a', 'bb');
Insert into WORK_AUDIT
   (G_DATE, P_KEY, P_STATUS, P_USER, P_CD)
 Values
   (TO_DATE('3/3/2019', 'MM/DD/YYYY'), 2, 'new', 'd', 'ab');
Insert into WORK_AUDIT
   (G_DATE, P_KEY, P_STATUS, P_USER, P_CD)
 Values
   (TO_DATE('3/3/2019', 'MM/DD/YYYY'), 3, 'med', 'd', 'cb');
Insert into WORK_AUDIT
   (G_DATE, P_KEY, P_STATUS, P_USER, P_CD)
 Values
   (TO_DATE('3/3/2019', 'MM/DD/YYYY'), 4, 'new', 'c', 'cb');
Insert into WORK_AUDIT
   (G_DATE, P_KEY, P_STATUS, P_USER, P_CD)
 Values
   (TO_DATE('3/4/2019', 'MM/DD/YYYY'), 1, 'med', 'd', 'bb');
Insert into WORK_AUDIT
   (G_DATE, P_KEY, P_STATUS, P_USER, P_CD)
 Values
   (TO_DATE('3/4/2019', 'MM/DD/YYYY'), 2, 'old', 'd', 'xy');
Insert into WORK_AUDIT
   (G_DATE, P_KEY, P_STATUS, P_USER, P_CD)
 Values
   (TO_DATE('3/4/2019', 'MM/DD/YYYY'), 3, 'med', 'd', 'cb');
Insert into WORK_AUDIT
   (G_DATE, P_KEY, P_STATUS, P_USER, P_CD)
 Values
   (TO_DATE('3/4/2019', 'MM/DD/YYYY'), 5, 'new', 'c', 'cb');

COMMIT;

Upvotes: 0

Views: 63

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

If I understand correctly, this is a gaps-and-islands problem.

This should do what you want:

select p_key, p_status, p_user, p_cd, min(g_date) as start_dt,
       (case when max_g_date = max(g_date) then date '4712-12-31' else max(g_date) end) as end_dt,
       (case when max_g_date = max(g_date) then 1 else 0 end) as latest_row_flag
from (select wa.*,
             row_number() over (partition by p_key order by g_date) as seqnum,
             row_number() over (partition by p_key, p_status, p_user, p_cd order by g_date) as seqnum_2,
             max(g_date) over () as max_g_date
      from work_audit wa
     ) wa
group by p_key, p_status, p_user, p_cd, (seqnum - seqnum_2), max_g_date
order by p_key, min(g_date);

Here is a db<>fiddle. The fiddle uses Postgres because Oracle doesn't work as well with db<>fiddle. But the query doesn't change.

Upvotes: 1

Related Questions