Finding Competitor Prices for the latest Updated Date

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

hi arun

i am trying to do this

it will help a lot
if things are clear

please try to explain what you want
with the help of diagrams ( small small data bits to explain)
one or two words
etc etc

example
id name
1 harish
2 varun
2 jack
here in 2 varun 2 jack
i want only max of name

understanding what you want is the ONLY hard part
rest is very easy

:slight_smile: :slight_smile:

hi arun

what does all this mean

your comments
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'

please explain in T_SQL language

Hi Harish ,

Sorry for my late response...Will get back to you soon..

Thanks,
Arun

hi arun

i got till here ....

drop create data ...
/***************************************************************************/


-- drop all temp tables 

DECLARE @SQL NVARCHAR(max) = (SELECT 'DROP TABLE ' + Stuff( ( SELECT ',' + NAME 
                                     FROM 
                                     tempdb.sys.tables FOR 
                                       xml path( 
          '') ), 1, 1, '')); 

--PRINT @SQL; 
EXEC Sp_executesql 
  @SQL; 


/***************************************************************************/
 
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')

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')

/****************************************************************************/

-- select from all temp tables 

DECLARE @SQL1 NVARCHAR(max) = (SELECT Stuff((SELECT '; select * from ' + NAME + 
                                                    ' go ' 
                 FROM   tempdb.sys.tables 
                 FOR xml path('')), 1, 1, '')); 

--PRINT @SQL1; 
EXEC Sp_executesql 
  @SQL1; 

/***************************************************************************/
SQL ...
/***************************************************************************/

-- SELECT Query SOLUTION 
; WITH cte 
     AS (SELECT itemid, 
                price AS okjprice 
         FROM   #input 
         WHERE  seller = 'okj' 
                AND isfba <> 1), 
     cte1 
     AS (SELECT itemid, 
                price AS lowestprice 
         FROM   #input a 
         WHERE  is_bb = 1 
                AND last_checked = (SELECT Max(last_checked) 
                                    FROM   #input b 
                                    WHERE  b.itemid = a.itemid)) 
SELECT a.itemid, 
       a.okjprice, 
       b.lowestprice 
FROM   cte a 
       JOIN cte1 b 
         ON a.itemid = b.itemid     
/***************************************************************************/

Hi Harish ,

Following is the output query that worked..

;with cte1 as (
select *, max(last_checked)over(partition by itemid) as latestdate
from #input),
cte as(
select itemid,min(price)as lowestprice,count(seller)as noofcompetitiors
from cte1
where last_checked=latestdate
and seller <> 'amxcd'and isfba<>1
group by itemid),
cte2 as (
select itemid, count(seller)as a
from cte1
where last_checked=latestdate
and seller='okj'
group by itemid),
cte3 as (
select *,
case when itemid in (select itemid from cte2) then 'YES'
else 'NO'
end as isokjacompetetior
from cte)

select * from (
select b.itemid,a.price as okjprice,b.lowestprice,b.noofcompetitiors,b. isokjacompetetior from (
select itemid,price
from cte1
where seller='okj')a
right join cte3 b
on a.itemid = b.itemid) b
where okjprice is not null

Arun

Nice

As long as you got solution..
Fine ..

. That's the only thing i am looking for

:+1::+1::slightly_smiling_face::slightly_smiling_face: