Postgres 12

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

select version();


version


PostgreSQL 12.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit


create table public.complaints (
       complaintid integer 
     , datetime    timestamp 
);
 
insert into complaints(complaintid, datetime)
       select cid, to_timestamp(dtm,'mm/dd/yyyy hh:mi')::timestamp 
         from ( with comp_list(cid, dtm) as  
                     ( values ( 1, '12/24/2019 1:07 PM')
                            , ( 2, '12/24/2019 1:20 PM')
                            , ( 3, '12/24/2019 1:40 PM')
                            , ( 4, '12/24/2019 2:00 PM')
                            , ( 5, '12/24/2019 2:10 PM')
                            , ( 6, '12/24/2019 2:12 PM')
                            , ( 7, '12/24/2019 2:50 PM')
                            , ( 8, '12/24/2019 2:55 PM')
                            , ( 9, '12/24/2019 3:00 PM')
                            , (10, '12/24/2019 3:08 PM')
                            , (11, '12/24/2019 4:00 PM')
                            , (12, '12/24/2019 4:50 PM')
                            , (13, '12/24/2019 7:00 PM')
                            , (14, '12/26/2019 7:01 PM')
                     )
                select * from comp_list 
              )  s;                     
              
select * from complaints; 
14 rows affected



complaintid
datetime


1
2019-12-24 01:07:00

2
2019-12-24 01:20:00

3
2019-12-24 01:40:00

4
2019-12-24 02:00:00

5
2019-12-24 02:10:00

6
2019-12-24 02:12:00

7
2019-12-24 02:50:00

8
2019-12-24 02:55:00

9
2019-12-24 03:00:00

10
2019-12-24 03:08:00

11
2019-12-24 04:00:00

12
2019-12-24 04:50:00

13
2019-12-24 07:00:00

14
2019-12-26 07:01:00


create or replace function public.accept_reject_complaints()
 returns table( o_complaintid integer
              , o_datetime    timestamp 
              , o_status      text
              )
 language plpgsql
AS $$                 
declare
    l_current_end_ts timestamp = '-infinity'::timestamp;

    c_complaint_list cursor for
                     select complaintid, datetime     
                       from complaints
                      order by datetime;
begin
    for complaint_rec in c_complaint_list 
    loop
       if complaint_rec.datetime  > l_current_end_ts then 
          o_status = 'Accept'; 
          l_current_end_ts = complaint_rec .datetime  + interval '1 hour';
       else 
          o_status = 'Reject'; 
       end if; 
   
       o_datetime = complaint_rec.datetime;
       o_complaintid = complaint_rec.complaintid;
       return next; 
    end loop ;

end ; 
$$;
select o_complaintid complaintid 
     , o_datetime    datetime 
     , o_status      status 
  from accept_reject_complaints();


complaintid
datetime
status


1
2019-12-24 01:07:00
Accept

2
2019-12-24 01:20:00
Reject

3
2019-12-24 01:40:00
Reject

4
2019-12-24 02:00:00
Reject

5
2019-12-24 02:10:00
Accept

6
2019-12-24 02:12:00
Reject

7
2019-12-24 02:50:00
Reject

8
2019-12-24 02:55:00
Reject

9
2019-12-24 03:00:00
Reject

10
2019-12-24 03:08:00
Reject

11
2019-12-24 04:00:00
Accept

12
2019-12-24 04:50:00
Reject

13
2019-12-24 07:00:00
Accept

14
2019-12-26 07:01:00
Accept

complaintid	datetime
1	2019-12-24 01:07:00
2	2019-12-24 01:20:00
3	2019-12-24 01:40:00
4	2019-12-24 02:00:00
5	2019-12-24 02:10:00
6	2019-12-24 02:12:00
7	2019-12-24 02:50:00
8	2019-12-24 02:55:00
9	2019-12-24 03:00:00
10	2019-12-24 03:08:00
11	2019-12-24 04:00:00
12	2019-12-24 04:50:00
13	2019-12-24 07:00:00
14	2019-12-26 07:01:00

complaintid	datetime	status
1	2019-12-24 01:07:00	Accept
2	2019-12-24 01:20:00	Reject
3	2019-12-24 01:40:00	Reject
4	2019-12-24 02:00:00	Reject
5	2019-12-24 02:10:00	Accept
6	2019-12-24 02:12:00	Reject
7	2019-12-24 02:50:00	Reject
8	2019-12-24 02:55:00	Reject
9	2019-12-24 03:00:00	Reject
10	2019-12-24 03:08:00	Reject
11	2019-12-24 04:00:00	Accept
12	2019-12-24 04:50:00	Reject
13	2019-12-24 07:00:00	Accept
14	2019-12-26 07:01:00	Accept