Reputation: 615
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
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