Hi All,
Hope you are doing well!...I am trying to understand the duration at which specific sellers sell an item for ...Also the seller which sells the item for the longest duration... The sellers selling at the optimal price is denoted by to_seller (I am trying to calculate the duration in hours until which the seller stays in to_seller before changing to from_seller )...
Also one thing which I have not included in my dataset below is that when the to_seller is NULL that datapoint needs to be excluded in the calculations and also NULL should not be included as a seller
In my data ASIN represents an item...Please find the DDL below! (input as #input and expected output as #output)..Can you please help here..
create table #input
(ts datetime2,
ASIN varchar(20),
from_seller varchar(20),
to_seller varchar(20),
from_price float,
to_price float)
insert into #input values
('2019-09-30 04:28:46.143','YUIOP','Julong','welong','59','29'),
('2019-09-25 04:28:46.143','YUIOP','jklui','Julong','67','59'),
('2019-09-20 04:28:46.143','YUIOP','welong','Jklui','72','67'),
('2019-09-15 04:28:46.143','YUIOP','jkliop','welong','78','72'),
('2019-09-30 04:28:46.143','UIOPER','luioper','hjklo','123','130'),
('2019-09-30 04:28:46.143','UIOPER','luioper','hjklo','123','130'),
('2019-09-20 04:28:46.143','UIOPER','julong','welong','120','112'),
('2019-09-11 04:28:46.143','UIOPER','jklop','julong','121.5','120')
select * from #input
create table #output
(ASIN varchar(20),
welonghours float,
julonghours float,
longestdurationseller varchar(20),
longestdurationhours varchar(20)
)
insert into #output values
('YUIOP','120','72','jklui','168'),
('UIOPER','24.5','216','julong','216')
Thanks,
Arun