Looking for the same Trader buying and selling the same product within the 3minutes

Below I’ve the example table

    Create Table #A 
(
    Time nvarchar(70),
    Trader nvarchar(30),
    Product nvarchar(30),
    [Buy/Sell] nvarchar(30)
)

Insert into #A Values

('2019-03-01T14:22:29z', 'Jhon', 'Apple', 'Buy'),
('2019-03-01T12:35:09z', 'Jhon', 'Orange', 'Sell'),
('2019-03-01T12:35:09z', 'Mary', 'Milk', 'Buy'),
('2019-03-01T12:35:10z', 'Susan', 'Milk', 'Buy'),
('2019-03-01T12:35:23z', 'Tom', 'Bread', 'Sell'),
('2019-03-01T14:15:52z', 'Jhon', 'Apple', 'Sell'),
('2019-03-01T14:15:53z', 'Tom', 'Orange', 'Sell'),
('2019-03-01T14:22:33z', 'Mary', 'Apple', 'Buy'),
('2019-03-01T14:22:37z', 'Mary', 'Orange', 'Sell'),
('2019-03-01T12:37:41z', 'Susan', 'Milk', 'Buy'),
('2019-03-01T12:37:41z', 'Susan', 'Milk', 'Buy')

Select * from #A

Basically I’m to get the same Trader buying and selling the same product within the 3minutes

Below I’ve tried this but not the correct one and working

;With DateTimeTbl
as
(
select SUBSTRING(a.Time,1,10) date, SUBSTRING(a.Time,12,8) Time1, a.*
-- lead(Time) over(order by time) cnt 
from #A a 
),
DataTbl
as
(
Select d.*, row_number() over(Partition by d.Trader,d.product order by d.time1) CntSrs  
from DateTimeTbl d 
--where [buy/sell] = 'Sell'
)
Select lag(Time1) over(order by time) cnt, d.*  from DataTbl d where CntSrs>1

Answer

Basically I’m to get the same Trader buying and selling the same product within the 3minutes

I would suggest lead(). To get the first record:

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;

Note: This assumes that buy_sell takes on only two values, which is consistent with your sample data.

Here is a db<>fiddle. Note that it fixes the data types to be appropriate (for the time column) and renames the last column so it does not need to be escaped.