Bridging Data from Different Tables for Vendor Promotions

Hi All,

Hope you are doing well!... I am trying to bridge data from different tables to create the promotion table vendor list ..I have provided the list of input tables with DDL and also the output table with DDL...Also following are the set of rules that I want to build the output table using..

  1. In table #productdetail I need to pick items that have available =1 and notsellable =0 and also available =2 notsellable=0 and also expected should be less than 5th august 2019..

  2. From #product table I need to join id with pid in #productdetail and coalesce (vendor_id to get the vendor_id

3)Using the vendor_id that I have got from the step above i need to get the vendorname from #vendor table..

  1. From the #Asin table I need to select the latest lastconfirmedasin from the list of items selected in step 1

  2. From the #pageviews table I need to get the sum of page views based on the lastconfirmedasin for the list of items selected in step 1

  3. Then in the promo table I need to select the average promo% got for the items selected in step 1..The average promo% is calculated as avg((pre_promo_price-promo_price)/promo_price*100))

Can you please help me here!..

create table #productdetail
(itemid int,
available int,
expected VARCHAR(50),
isnotsellable int,
pid int,
vendor_id VARCHAR(50))

insert into #productdetail values
('123','1','null','1','1','201'),
('125','2','8/5/2019','0','2','null'),
('127','1','null','0','3','202'),
('129','2','null','0','4','203'),
('131','1','null','0','5','204'),
('133','1','null','1','6','205'),
('135','1','null','0','7','206'),
('137','1','null','0','8','207'),
('139','1','null','0','9','208'),
('141','1','null','0','10','209'),
('143','1','null','0','11','210'),
('145','1','null','0','12','211'),
('147','1','null','1','13','212'),
('149','1','null','0','14','213'),
('151','1','null','0','15','214'),
('153','1','null','0','16','null'),
('155','2','8/29/2019','1','17','215'),
('157','2','8/4/2019','1','18','216'),
('159','2','8/6/2019','0','19','217'),
('161','2','8/3/2019','0','20','218'),
('163','2','8/15/2019','0','21','null')

CREATE Table #product
(id int,
vendor_id varchar(50)
)

insert into #product values
('1','201'),
('2','267'),
('3','202'),
('4','203'),
('5','204'),
('6','205'),
('7','null'),
('8','207'),
('9','208'),
('10','209'),
('11','210'),
('12','211'),
('13','212'),
('14','213'),
('15','214'),
('16','215'),
('17','215'),
('18','216'),
('19','null'),
('20','218'),
('21','219')

create table #vendor
(vendorname varchar(50),
vendor_id int)

insert into #vendor values
('cola','201'),
('fola','202'),
('foam','203'),
('choam','204'),
('somb','205'),
('chill','206'),
('fill','207'),
('comer','208'),
('salon','209'),
('falon','210'),
('chiran','211'),
('feran','212'),
('meran','213'),
('Konjan','214'),
('fedan','215'),
('Hummer','216'),
('cholp','217'),
('sulps','218'),
('culp','219'),
('jko','267')

Create table #asin
(item_id int,
lastconfirmed datetime,
lastconfirmedasin varchar(50)
)

insert into #asin values
('123','2018-12-19 22:19:07.357','iopyu'),
('123','2016-12-19 22:19:07.357','hjyug'),
('125','2019-05-19 22:19:07.357','uirty'),
('125','2016-12-19 22:19:07.357','1yuio'),
('127','2019-02-19 22:19:07.357','klbnm'),
('127','2018-12-19 22:19:07.357','lopgh'),
('127','2016-12-19 22:19:07.357','nmbh'),
('129','2016-11-19 22:19:07.357','jklh'),
('131','2019-11-19 22:19:07.357','werat'),
('133','2019-06-19 22:19:07.357','vbnwe'),
('133','2019-01-19 22:19:07.357','mnwer'),
('133','2017-11-19 22:19:07.357','sdert'),
('135','2019-06-19 22:19:07.357','vbsdx'),
('137','2017-06-19 22:19:07.357','bnxct')

create table #pageviews
(startdate varchar(50),
lastconfirmedasin varchar(50),
pageviews int)

insert into #pageviews
values
('42952','hjyug','102'),
('42983','hjyug','201'),
('43743','hjyug','1002'),
('43348','iopyu','345'),
('43378','iopyu','545'),
('43621','1yuio','300'),
('43622','1yuio','200'),
('43712','uirty','150'),
('15/4/2019','uirty','200'),
('15/4/2019','nmbh','300'),
('15/4/2019','lopgh','400'),
('15/4/2019','klbnm','500'),
('16/4/2019','klbnm','1000'),
('15/4/2019','jklh','600'),
('15/4/2019','werat','700'),
('15/4/2019','sdert','800'),
('15/4/2019','mnwer','900'),
('15/4/2019','vbnwe','1000'),
('15/4/2019','vbsdx','1100'),
('25/4/2019','vbsdx','3000'),
('15/4/2019','bnxct','1200'),
('31/4/2019','bnxct','2200')

create table #promo
(itemid int,
promo_price float,
pre_promo_price float,
promo_start varchar(50),
promo_end varchar(50)
)

insert into #promo values
('123','214.7','220','43149','2/30/2018'),
('123','225','230','43132','43146'),
('125','400','430','43497','43506'),
('125','380','390','43511','2/30/2019'),
('127','120','140','43539','43554'),
('129','80','100','43570','43585'),
('129','110','120','43556','43565'),
('131','80','100','43497','43511'),
('131','110','120','43475','43480'),
('133','230','420','43475','43480'),
('135','250','440','43475','43480'),
('137','270','460','43475','43480'),
('139','290','480','43475','43480'),
('141','310','500','43475','43480'),
('143','330','520','43475','43480'),
('145','350','540','43475','43480')

create table #output
(itemid int,
available int,
expected varchar(50),
isnotsellable int,
pid int,
vendor_id int,
vendorname varchar(50),
lastconfirmedasin varchar(50),
pageviews int,
promoavg float)

insert into #output values
('127','1','null','0','3','202','fola','klbnm','1500','16.6666666666667'),
('131','1','null','0','5','204','choam','werat','700','17.0454545454545'),
('135','1','null','0','7','206','chill','vbsdx','4400','76'),
('137','1','null','0','8','207','fill','bnxct','3400','70.3703703703704'),
('139','1','null','0','9','208','comer','','','65.5172413793103'),
('141','1','null','0','10','209','salon','','','61.2903225806452'),
('143','1','null','0','11','210','falon','','','57.5757575757576'),
('145','1','null','0','12','211','chiran','','','54.2857142857143'),
('149','1','null','0','14','213','meran','','',''),
('151','1','null','0','15','214','Konjan','','',''),
('153','1','null','0','16','215','fedan','','',''),
('161','2','43680','0','20','218','sulps','','',''),
('125','2','43682','0','2','267','jko','uirty','350','5.06578947368421')

Arun

hi arun

i tried to do this !!!

this is how far I got
please let me know whats required before we get the final answer
:slight_smile: :slight_smile:

drop create sample 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 #productdetail
(itemid int,
available int,
expected VARCHAR(50),
isnotsellable int,
pid int,
vendor_id VARCHAR(50))

insert into #productdetail values
('123','1','null','1','1','201'),
('125','2','8/5/2019','0','2','null'),
('127','1','null','0','3','202'),
('129','2','null','0','4','203'),
('131','1','null','0','5','204'),
('133','1','null','1','6','205'),
('135','1','null','0','7','206'),
('137','1','null','0','8','207'),
('139','1','null','0','9','208'),
('141','1','null','0','10','209'),
('143','1','null','0','11','210'),
('145','1','null','0','12','211'),
('147','1','null','1','13','212'),
('149','1','null','0','14','213'),
('151','1','null','0','15','214'),
('153','1','null','0','16','null'),
('155','2','8/29/2019','1','17','215'),
('157','2','8/4/2019','1','18','216'),
('159','2','8/6/2019','0','19','217'),
('161','2','8/3/2019','0','20','218'),
('163','2','8/15/2019','0','21','null')

CREATE Table #product
(id int,
vendor_id varchar(50)
)

insert into #product values
('1','201'),
('2','267'),
('3','202'),
('4','203'),
('5','204'),
('6','205'),
('7','null'),
('8','207'),
('9','208'),
('10','209'),
('11','210'),
('12','211'),
('13','212'),
('14','213'),
('15','214'),
('16','215'),
('17','215'),
('18','216'),
('19','null'),
('20','218'),
('21','219')

create table #vendor
(vendorname varchar(50),
vendor_id int)

insert into #vendor values
('cola','201'),
('fola','202'),
('foam','203'),
('choam','204'),
('somb','205'),
('chill','206'),
('fill','207'),
('comer','208'),
('salon','209'),
('falon','210'),
('chiran','211'),
('feran','212'),
('meran','213'),
('Konjan','214'),
('fedan','215'),
('Hummer','216'),
('cholp','217'),
('sulps','218'),
('culp','219'),
('jko','267')

Create table #asin
(item_id int,
lastconfirmed datetime,
lastconfirmedasin varchar(50)
)

insert into #asin values
('123','2018-12-19 22:19:07.357','iopyu'),
('123','2016-12-19 22:19:07.357','hjyug'),
('125','2019-05-19 22:19:07.357','uirty'),
('125','2016-12-19 22:19:07.357','1yuio'),
('127','2019-02-19 22:19:07.357','klbnm'),
('127','2018-12-19 22:19:07.357','lopgh'),
('127','2016-12-19 22:19:07.357','nmbh'),
('129','2016-11-19 22:19:07.357','jklh'),
('131','2019-11-19 22:19:07.357','werat'),
('133','2019-06-19 22:19:07.357','vbnwe'),
('133','2019-01-19 22:19:07.357','mnwer'),
('133','2017-11-19 22:19:07.357','sdert'),
('135','2019-06-19 22:19:07.357','vbsdx'),
('137','2017-06-19 22:19:07.357','bnxct')

create table #pageviews
(startdate varchar(50),
lastconfirmedasin varchar(50),
pageviews int)

insert into #pageviews
values
('42952','hjyug','102'),
('42983','hjyug','201'),
('43743','hjyug','1002'),
('43348','iopyu','345'),
('43378','iopyu','545'),
('43621','1yuio','300'),
('43622','1yuio','200'),
('43712','uirty','150'),
('15/4/2019','uirty','200'),
('15/4/2019','nmbh','300'),
('15/4/2019','lopgh','400'),
('15/4/2019','klbnm','500'),
('16/4/2019','klbnm','1000'),
('15/4/2019','jklh','600'),
('15/4/2019','werat','700'),
('15/4/2019','sdert','800'),
('15/4/2019','mnwer','900'),
('15/4/2019','vbnwe','1000'),
('15/4/2019','vbsdx','1100'),
('25/4/2019','vbsdx','3000'),
('15/4/2019','bnxct','1200'),
('31/4/2019','bnxct','2200')

create table #promo
(itemid int,
promo_price float,
pre_promo_price float,
promo_start varchar(50),
promo_end varchar(50)
)

insert into #promo values
('123','214.7','220','43149','2/30/2018'),
('123','225','230','43132','43146'),
('125','400','430','43497','43506'),
('125','380','390','43511','2/30/2019'),
('127','120','140','43539','43554'),
('129','80','100','43570','43585'),
('129','110','120','43556','43565'),
('131','80','100','43497','43511'),
('131','110','120','43475','43480'),
('133','230','420','43475','43480'),
('135','250','440','43475','43480'),
('137','270','460','43475','43480'),
('139','290','480','43475','43480'),
('141','310','500','43475','43480'),
('143','330','520','43475','43480'),
('145','350','540','43475','43480')

create table #output
(itemid int,
available int,
expected varchar(50),
isnotsellable int,
pid int,
vendor_id int,
vendorname varchar(50),
lastconfirmedasin varchar(50),
pageviews int,
promoavg float)

insert into #output values
('127','1','null','0','3','202','fola','klbnm','1500','16.6666666666667'),
('131','1','null','0','5','204','choam','werat','700','17.0454545454545'),
('135','1','null','0','7','206','chill','vbsdx','4400','76'),
('137','1','null','0','8','207','fill','bnxct','3400','70.3703703703704'),
('139','1','null','0','9','208','comer','','','65.5172413793103'),
('141','1','null','0','10','209','salon','','','61.2903225806452'),
('143','1','null','0','11','210','falon','','','57.5757575757576'),
('145','1','null','0','12','211','chiran','','','54.2857142857143'),
('149','1','null','0','14','213','meran','','',''),
('151','1','null','0','15','214','Konjan','','',''),
('153','1','null','0','16','215','fedan','','',''),
('161','2','43680','0','20','218','sulps','','',''),
('125','2','43682','0','2','267','jko','uirty','350','5.06578947368421')

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

-- 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 c.vendorname , 
       d.lastconfirmedasin , 
       'average promo % ' -- how to calculate this ???? 
FROM   #productdetail a 
JOIN   #product b 
ON     a.pid = b.id 
JOIN   #vendor c 
ON     c.vendor_id = b.vendor_id 
JOIN   #asin d 
ON     d.item_id = a.itemid 
JOIN   #pageviews e 
ON     e.lastconfirmedasin = d.lastconfirmedasin 
JOIN   #promo f 
ON     f.itemid = a.itemid 
WHERE  a.available IN ( 1,2) 
AND    a.isnotsellable = 0 
AND    try_parse(a.expected as date using 'en-US') < try_parse('8/5/2019' AS date using 'en-US')

using the sample data i am not getting any results

Hi Harish,

I was able to get the answer for this...

Thanks,
Arun