Skip to content
Advertisement

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

Advertisement

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.

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement