Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table ora_table (id number, time_data timestamp, status varchar2(30));
insert into ora_table values (1, to_timestamp('2019-10-20 12:34:56.000', 'yyyy-mm-dd hh24:mi:ss.ff'), 'approved');
1 rows affected

insert into ora_table values (1, to_timestamp('2019-10-22 12:34:56.000', 'yyyy-mm-dd hh24:mi:ss.ff'), 'approved');
1 rows affected

insert into ora_table values (2, to_timestamp('2019-10-20 17:34:56.000', 'yyyy-mm-dd hh24:mi:ss.ff'), 'approved');
1 rows affected

insert into ora_table values (2, to_timestamp('2019-10-21 12:34:56.000', 'yyyy-mm-dd hh24:mi:ss.ff'), 'approved');
1 rows affected

insert into ora_table values (3, to_timestamp('2019-10-23 18:10:10.000', 'yyyy-mm-dd hh24:mi:ss.ff'), 'mod_in_ip');
1 rows affected

insert into ora_table values (3, to_timestamp('2019-10-24 11:10:10.000', 'yyyy-mm-dd hh24:mi:ss.ff'), 'approved');
1 rows affected

insert into ora_table values (3, to_timestamp('2019-10-24 12:10:10.000', 'yyyy-mm-dd hh24:mi:ss.ff'), 'approved');
1 rows affected

insert into ora_table values (4, to_timestamp('2019-10-25 12:10:10.000', 'yyyy-mm-dd hh24:mi:ss.ff'), 'approved');
1 rows affected

insert into ora_table values (4, to_timestamp('2019-10-25 18:10:10.000', 'yyyy-mm-dd hh24:mi:ss.ff'), 'approved');
1 rows affected

select * from ora_table;


ID
TIME_DATA
STATUS


1
20-OCT-19 12.34.56.000000 PM
approved

1
22-OCT-19 12.34.56.000000 PM
approved

2
20-OCT-19 05.34.56.000000 PM
approved

2
21-OCT-19 12.34.56.000000 PM
approved

3
23-OCT-19 06.10.10.000000 PM
mod_in_ip

3
24-OCT-19 11.10.10.000000 AM
approved

3
24-OCT-19 12.10.10.000000 PM
approved

4
25-OCT-19 12.10.10.000000 PM
approved

4
25-OCT-19 06.10.10.000000 PM
approved


select id
        ,status 
        ,trunc(time_data) from ora_table 
 where status='approved' 
 and id in (1,2,3,4)
 group by id,status,trunc(time_data)


ID
STATUS
TRUNC(TIME_DATA)


1
approved
22-OCT-19

2
approved
20-OCT-19

4
approved
25-OCT-19

3
approved
24-OCT-19

1
approved
20-OCT-19

2
approved
21-OCT-19


select id,
       case
           when count(id)<=1 then 'New' 
           else 'Edited' 
       End AS prefix 
from
(select id
        ,status 
        ,trunc(time_data) from ora_table 
 where status='approved' 
 and id in (1,2,3,4)
 group by trunc(time_data), id, status
)
group by id;


ID
PREFIX


1
Edited

2
Edited

4
New

3
New

ID	TIME_DATA	STATUS
1	20-OCT-19 12.34.56.000000 PM	approved
1	22-OCT-19 12.34.56.000000 PM	approved
2	20-OCT-19 05.34.56.000000 PM	approved
2	21-OCT-19 12.34.56.000000 PM	approved
3	23-OCT-19 06.10.10.000000 PM	mod_in_ip
3	24-OCT-19 11.10.10.000000 AM	approved
3	24-OCT-19 12.10.10.000000 PM	approved
4	25-OCT-19 12.10.10.000000 PM	approved
4	25-OCT-19 06.10.10.000000 PM	approved

ID	STATUS	TRUNC(TIME_DATA)
1	approved	22-OCT-19
2	approved	20-OCT-19
4	approved	25-OCT-19
3	approved	24-OCT-19
1	approved	20-OCT-19
2	approved	21-OCT-19