oracle sql Sort dated transactions by order of transaction

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.

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