SQLize Online / PHPize Online  /  SQLtest Online

create table task ( employee_id integer, application_id integer, client_id integer, application_begin_date date, issuance_date date ); insert into task values (1, 12, 5, to_date('01.01.2023', 'dd.mm.yyy'), null), (2, 23, 5, to_date('02.02.2023', 'dd.mm.yyy'), null), (3, 34, 5, to_date('15.03.2023', 'dd.mm.yyy'), null), (4, 45, 5, to_date('25.03.2023', 'dd.mm.yyy'), to_date('01.04.2023', 'dd.mm.yyy')), (5, 65, 6, to_date('01.01.2023', 'dd.mm.yyy'), null), (6, 68, 6, to_date('15.01.2023', 'dd.mm.yyy'), to_date('25.01.2023', 'dd.mm.yyy')), (7, 78, 7, to_date('01.01.2023', 'dd.mm.yyy'), null), (8, 97, 7, to_date('02.02.2023', 'dd.mm.yyy'), null), (9, 90, 8, to_date('01.01.2023', 'dd.mm.yyy'), null), (19, 80, 8, to_date('02.02.2023', 'dd.mm.yyy'), null), (11, 98, 8, to_date('15.02.2023', 'dd.mm.yyy'), to_date('20.02.2023', 'dd.mm.yyy')), (12, 96, 9, to_date('01.01.2023', 'dd.mm.yyy'), null), (13, 46, 9, to_date('03.03.2023', 'dd.mm.yyy'), null); select * from task; select employee_id, application_id, client_id, application_begin_date, i_dt as issuance_date from ( select *, row_number() over(partition by client_id order by application_begin_date, application_id) as rn from ( select *, max(issuance_date) over(partition by client_id) as i_dt from task )t where i_dt - application_begin_date <= 30 ) foo where rn = 1