I have been working on this for a few weeks and I know there’s a way like using ROW_NUMBER(), but I’m just not too familiar with it, Or maybe there’s a better way….
Select * from trans_rec where tran_date <= '4-mar-2020' and tran_date >= '19-jun-2020'
shows this:
Tran_date SKU type tran_id 19-jun-2020 555123 arrive GD001X60 18-jun-2020 555123 ship GD001X60 18-jun-2020 555454 arrive GD001X59 17-jun-2020 555454 ship GD001X59 17-jun-2020 555123 Man-add GD001X60 16-jun-2020 555123 Man-del GD123457 16-jun-2020 555454 Man-add GD001X59 15-jun-2020 555454 Man-del GD123456 3-jun-2020 555454 Man-del GD1230013 29-mar-2020 555123 sys-mer GD123457 27-mar-2020 555123 sys-del GD1230164 27-mar-2020 555123 sys-del GD1230114 25-mar-2020 555454 sys-mer GD123456 25-mar-2020 555123 bal 25-mar-2020 555123 sys-add GD1230164 25-mar-2020 555123 arrive 24-mar-2020 555454 sys-del GD1231011 24-mar-2020 555454 sys-del GD1230012 24-mar-2020 555454 arrive 24-mar-2020 555454 sys-add GD1231011 24-mar-2020 555454 sys-add GD1230012 23-mar-2020 555123 Man-add GD1230114 23-mar-2020 555454 Man-add GD1230013 4-mar-2020 555454 bal 4-mar-2020 555123 bal 4-mar-2020 555454 bal
It needs to show this:
SKU SYS_MERGE_DT latest_MAN_DEL_DT MANUAL_ADD_DT SHIP_DT ARRIVE_DT X_id 555123 29-mar-2020 16-jun-2020 17-jun-2020 18-jun-2020 19-jun-2020 GD001X60 555454 25-mar-2020 15-jun-2020 16-jun-2020 17-jun-2020 18-jun-2020 GD001X59
As you can see, there’s 2 delete dates for SKU 555454, and it has to show the lasted manual delete date of that SKU before a ship date of the same SKU.
Advertisement
Answer
Looks like you need something like this:
select
*
from (
select
ran_date,SKU,type
,max(tran_id)
keep(dense_rank first order by ran_date desc)
over(partition by SKU) as x_id
from trans_rec
)
pivot(
max(ran_date) max_dt
for type
in (
'arrive' as ARRIVE
,'sys-mer' as SYS_MERGE
,'ship' as SHIP
,'Man-del' as MANUAL_DEL
,'bal' as bal
)
);
Full example with test data:
with trans_rec(ran_date, SKU, type, tran_id) as (
select to_date('19-jun-2020','dd-mon-yyyy'), 555123,'arrive' ,'GD001X60' from dual union all
select to_date('18-jun-2020','dd-mon-yyyy'), 555123,'ship' ,'GD001X60' from dual union all
select to_date('18-jun-2020','dd-mon-yyyy'), 555454,'arrive' ,'GD001X59' from dual union all
select to_date('17-jun-2020','dd-mon-yyyy'), 555454,'ship' ,'GD001X59' from dual union all
select to_date('17-jun-2020','dd-mon-yyyy'), 555123,'Man-add','GD001X60' from dual union all
select to_date('16-jun-2020','dd-mon-yyyy'), 555123,'Man-del','GD123457' from dual union all
select to_date('16-jun-2020','dd-mon-yyyy'), 555454,'Man-add','GD001X59' from dual union all
select to_date('15-jun-2020','dd-mon-yyyy'), 555454,'Man-del','GD123456' from dual union all
select to_date('03-jun-2020','dd-mon-yyyy'), 555454,'Man-del','GD1230013' from dual union all
select to_date('29-mar-2020','dd-mon-yyyy'), 555123,'sys-mer','GD123457' from dual union all
select to_date('27-mar-2020','dd-mon-yyyy'), 555123,'sys-del','GD1230164' from dual union all
select to_date('27-mar-2020','dd-mon-yyyy'), 555123,'sys-del','GD1230114' from dual union all
select to_date('25-mar-2020','dd-mon-yyyy'), 555454,'sys-mer','GD123456' from dual union all
select to_date('25-mar-2020','dd-mon-yyyy'), 555123,'bal' ,null from dual union all
select to_date('25-mar-2020','dd-mon-yyyy'), 555123,'sys-add','GD1230164' from dual union all
select to_date('25-mar-2020','dd-mon-yyyy'), 555123,'arrive' ,null from dual union all
select to_date('24-mar-2020','dd-mon-yyyy'), 555454,'sys-del','GD1231011' from dual union all
select to_date('24-mar-2020','dd-mon-yyyy'), 555454,'sys-del','GD1230012' from dual union all
select to_date('24-mar-2020','dd-mon-yyyy'), 555454,'arrive' ,null from dual union all
select to_date('24-mar-2020','dd-mon-yyyy'), 555454,'sys-add','GD1231011' from dual union all
select to_date('24-mar-2020','dd-mon-yyyy'), 555454,'sys-add','GD1230012' from dual union all
select to_date('23-mar-2020','dd-mon-yyyy'), 555123,'Man-add','GD1230114' from dual union all
select to_date('23-mar-2020','dd-mon-yyyy'), 555454,'Man-add','GD1230013' from dual union all
select to_date('04-mar-2020','dd-mon-yyyy'), 555454,'bal' ,null from dual union all
select to_date('04-mar-2020','dd-mon-yyyy'), 555123,'bal' ,null from dual union all
select to_date('04-mar-2020','dd-mon-yyyy'), 555454,'bal' ,null from dual
)
select
*
from (
select
ran_date,SKU,type
,max(tran_id)
keep(dense_rank first order by ran_date desc)
over(partition by SKU) as x_id
from trans_rec
)
pivot(
max(ran_date) max_dt
for type
in (
'arrive' as ARRIVE
,'sys-mer' as SYS_MERGE
,'ship' as SHIP
,'Man-del' as MANUAL_DEL
,'bal' as bal
)
);
Result:
SKU X_ID ARRIVE_MAX_DT SYS_MERGE_MAX_DT SHIP_MAX_DT MANUAL_DEL_MAX_DT BAL_MAX_DT
---------- --------- ------------------- ------------------- ------------------- ------------------- -------------------
555454 GD001X59 2020-06-18 00:00:00 2020-03-25 00:00:00 2020-06-17 00:00:00 2020-06-15 00:00:00 2020-03-04 00:00:00
555123 GD001X60 2020-06-19 00:00:00 2020-03-29 00:00:00 2020-06-18 00:00:00 2020-06-16 00:00:00 2020-03-25 00:00:00