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.