Finding the duration of the sellers selling at a Optimal Price

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



@harishgg1 :Seeking your help here!...

So basically following are the facts:

A seller is actually selling the item if he is under the to_seller column....

The seller stops selling if the to_seller is occupied by another seller....

For example in my dataset above:

('2019-09-25 04:28:46.143','YUIOP','jklui','Julong','67','59'),
('2019-09-20 04:28:46.143','YUIOP','welong','Jklui','72','67')

Item YUIOP is sold by JKLUI on Sep 20th 2019 (JKLUI starts selling from Sep 20th ) Till then Welong was selling the item....Seller JKLUI sells the item YUIOP from 20th sep 04.28 to 25th Sep 4.28...Then Julong starts selling the item YUIOP...

So basically I am trying to calculate how much time Julong (seller) sells the item for and welong (seller) sells the item for...Along which seller sells the item for the longest duration and also the duration for which it is selling....

Hi Arun

I have started job
Very busy

If I get some time
Will definitely help you

Very very sorry

Other experts on this forum
Can help you

Please please excuse me

1 Like

Sure Harish!..No problem..All the best with your job!!