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..
-
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..
-
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..
-
From the #Asin table I need to select the latest lastconfirmedasin from the list of items selected in step 1
-
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
-
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