By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Create Table A
(
Time datetime,
Trader nvarchar(30),
Product nvarchar(30),
buy_sell nvarchar(30)
)
Insert into A Values
('2019-03-01T14:22:29', 'Jhon', 'Apple', 'Buy'),
('2019-03-01T12:35:09', 'Jhon', 'Orange', 'Sell'),
('2019-03-01T12:35:09', 'Mary', 'Milk', 'Buy'),
('2019-03-01T12:35:10', 'Susan', 'Milk', 'Buy'),
('2019-03-01T12:35:23', 'Tom', 'Bread', 'Sell'),
('2019-03-01T14:15:52', 'Jhon', 'Apple', 'Sell'),
('2019-03-01T14:15:53', 'Tom', 'Orange', 'Sell'),
('2019-03-01T14:22:33', 'Mary', 'Apple', 'Buy'),
('2019-03-01T14:22:37', 'Mary', 'Orange', 'Sell'),
('2019-03-01T12:37:41', 'Susan', 'Milk', 'Buy'),
('2019-03-01T12:37:41', 'Susan', 'Milk', 'Buy')
11 rows affected
select a.*
from (select a.*,
lead(time) over (partition by trader, product order by time) as next_time,
lead(buy_sell) over (partition by trader, product order by time) as next_buy_sell
from a a
) a
where next_time < dateadd(minute, 3, time) and
buy_sell <> next_buy_sell;
| Time | Trader | Product | buy_sell | next_time | next_buy_sell |
|---|
insert into a
values ('2019-03-01T12:37:42', 'Susan', 'Milk', 'Sell')
1 rows affected
select a.*
from (select a.*,
lead(time) over (partition by trader, product order by time) as next_time,
lead(buy_sell) over (partition by trader, product order by time) as next_buy_sell
from a a
) a
where next_time < dateadd(minute, 3, time) and
buy_sell <> next_buy_sell;
| Time | Trader | Product | buy_sell | next_time | next_buy_sell |
|---|---|---|---|---|---|
| 2019-03-01 12:37:41.000 | Susan | Milk | Buy | 2019-03-01 12:37:42.000 | Sell |