create table Operations ("Operator" text, dDate date, tTime time, sOper text);
insert into Operations values
('Иванова', '2020-06-01', '14:29:00', 'Расход'),
('Иванова', '2020-06-01', '14:30:01', 'Расход'),
('Иванова', '2020-06-01', '14:35:17', 'Приход'),
('Иванова', '2020-06-01', '14:30:25', 'Контроль'),
('Петрова', '2020-06-01', '14:35:18', 'Приход'),
('Петрова', '2020-06-01', '14:35:19', 'Приход'),
('Петрова', '2020-06-01', '14:35:20', 'Контроль');
---------------------------------------------------------------------------------------|
-- select * from Operations;
with t as (
select
*,
row_number() over(partition by "Operator", dDate order by tTime) as rn
from Operations
)
select
a."Operator",
a.dDate,
a.tTime as tOper,
b.tTime as tControl
from t as a, t as b
where a."Operator" = b."Operator"
and a.dDate = b.dDate
and a.rn = b.rn - 1
and b.sOper = 'Контроль';
---------------------------------------------------------------------------------------|
-- explain
select "Operator", dDate, tTime,
case when sOper = 'Контроль' then tTime else null end
-- max(tTime) filter (where sOper = 'Контроль') as tControl
from Operations
-- group by 1, 2
select * from Operations;
execute 'select * from Operations;'
Telegram: DB developers channel