SQLTeam.com | Weblogs | Forums

Finding duration of sellers selling in a month's duration

Hi All,

Hope you are doing well!... I am trying to find the timeframes of the sellers selling items in a months duration in hours...Also if the seller is NULL then those rows are to be neglected from the calculation purposes...Also since the consideration of the timeframe is for a month sep 1st and sep 30th should be included for calculation consideration purposes in my DDL below:

For example in terms of the illustration below:

('2019-09-07 12:42:58.290','T56YU','JOLER'),
('2019-09-05 13:42:00.087','T56YU','NULL'),
('2019-09-02 13:42:00.087','T56YU','VOGER'),

VOGER is actually selling the item from 2nd september to 7th September and the NULL row in between should be neglected...

Also in terms of the 1st seller in terms of the descending order of the dates sells till the 30th of sep if the date associated with the 1st seller is not the 30th of september....

('2019-09-09 01:52:18.863','T56YU','JOLER'),

So accordingly , JOLER sells till the 30th of september although the last row is the 9th of september associated with the ASIN T56YU and the seller JOLER...

Please find the DDL and expected output below:Can you please help here..

Create table #selling
(ts datetime2,
asin varchar(20),
seller varchar(40),
)

insert into #selling values
('2019-09-09 01:52:18.863','T56YU','JOLER'),
('2019-09-07 12:51:33.333','T56YU','VOGER'),
('2019-09-07 12:42:58.290','T56YU','JOLER'),
('2019-09-05 13:42:00.087','T56YU','NULL'),
('2019-09-02 13:42:00.087','T56YU','VOGER'),
('2019-09-02 13:31:14.210','T56YU','JOLER'),
('2019-09-01 13:31:14.210','T56YU','NULL')

create table #output
([asin] varchar (20),
seller varchar(20),
duration float)

insert into #output values
('T56YU','JOLER','504.2'),
('T56YU','VOGER','166')

Thanks,

Arun