Hi All,
Hope you are doing well!... I am trying to find the latest competitor prices for the Items from the table that has different last_checked dates(timestamps)...I am trying to find the price for OKJ seller and the lowest price for that item for the latest updated last_checked date..
- Isbb denotes the lowest price
*if amxcd is the seller for an item in the latest timestamp update then I should not select that item , Also if isfba =1 for an item for the latest timestamp date then I should not select that item
*Also I need to count the number of sellers for the items that I am selecting
- if okj is a competitor in the latest timestamp date then I need to say 'yes' else 'no'
Can you please help me here...Input and expected output tables are below:
--Input table
create table #input
(itemid varchar(50),
seller varchar(30),
price int,
last_checked datetime2,
is_bb int,
isfba int
)
insert into #input
values
('bclpa','amxcd','89','2019-07-17 14:04:00.000','1','1'),
('bclpa','cvbd','100','2019-07-17 14:04:00.000','0','0'),
('lpatt','cvbd','78','2019-04-17 14:04:00.000','0','0'),
('lpatt','bnhj','60','2019-04-17 14:04:00.000','1','0'),
('lpatt','okj','90','2019-04-17 14:04:00.000','0','0'),
('lpatt','cvbd','70','2018-04-17 14:04:00.000','1','0'),
('lpatt','hjnk','90','2018-04-17 14:04:00.000','0','1'),
('cjatt','klmn','45','2019-04-17 14:04:00.000','1','0'),
('cjatt','njkl','89','2019-04-17 14:04:00.000','0','1'),
('cjatt','lket','40','2018-04-17 14:04:00.000','1','0'),
('cjatt','jklop','76','2018-04-17 14:04:00.000','0','0'),
('tilo','okj','56','2019-02-17 14:04:00.000','1','0'),
('tilo','jklop','70','2019-02-17 14:04:00.000','0','0'),
('tilo','hjkl','88','2019-02-17 14:04:00.000','0','0'),
('tilo','amxcd','32','2018-02-17 14:04:00.000','1','0'),
('jkatt','jklop','22','2018-02-17 14:04:00.000','1','0'),
('jkatt','amxcd','56','2018-02-17 14:04:00.000','0','0'),
('katt','hjkl','90','2019-02-17 14:04:00.000','1','0'),
('katt','lklo','99','2019-02-17 14:04:00.000','0','0'),
('katt','bnm','108','2019-02-17 14:04:00.000','0','0'),
('katt','okj','78','2018-02-17 14:04:00.000','1','0'),
('katt','nmsd','80','2018-02-17 14:04:00.000','0','0')
----Ouptut table
create table #output
( itemid varchar(50),
okjprice int,
lowestprice int,
noofcompetitors int,
isokjacompetetior varchar(20)
)
insert into #output values
('lpatt','90','60','3','Yes'),
('tilo','56','56','3','yes'),
('katt','','90','3','No')
Arun