USE [AmazonShopForSoul]
GO
/****** Object: StoredProcedure [dbo].[auto_pricing_data_for_united_kingdom] Script Date: 2020-06-19 10:16:30 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
remove-this-phrase– =============================================
remove-this-phrase– Author: <Author,,Name>
remove-this-phrase– Create date: <Create Date,,>
remove-this-phrase– Description: <Description,,>
remove-this-phrase– =============================================
CREATE PROCEDURE [dbo].[auto_pricing_data_for_united_kingdom]
AS
BEGIN
remove-this-phrase– SET NOCOUNT ON added to prevent extra result sets from
remove-this-phrase– interfering with SELECT statements.
SET NOCOUNT ON;
/*
select p.sku, amazon_special_price_gbp as price, amazon_gbp_minimum_price as [minimum-seller-allowed-price], amazon_list_price_gbp as [maximum-seller-allowed-price], 1 as quantity, 3 as [handling-time], ‘default’ as [fulfillment-channel]
from price_calculations p
inner join [uk-Inventory+Report+07-03-2018] uk on uk.sku=p.sku
*/
/*
select p.sku, amazon_special_price_gbp as price, amazon_gbp_minimum_price as [minimum-seller-allowed-price], amazon_list_price_gbp as [maximum-seller-allowed-price], 1 as quantity, 3 as [handling-time], ‘default’ as [fulfillment-channel]
from price_calculations_jul_11_2018 p
inner join [UkAllListingsReport07112018] uk on uk.[seller-sku]=p.sku
*/
select * from UkAllListingsReport07182018 where status = ‘Active’
remove-this-phrase–on july 12th, 2018
select p.sku, round(amazon_special_price_gbp,2) as price
, round(amazon_gbp_minimum_price,2) as [minimum-seller-allowed-price]
, round(amazon_list_price_gbp,2) as [maximum-seller-allowed-price], 1 as quantity, 4 as [handling-time]
, ‘default’ as [fulfillment-channel]
from price_calculations_jul_17_2018 p
inner join UkAllListingsReport07182018 uk on uk.[seller-sku]=p.sku
where (uk.status=’Active’) and (p.status=1)
remove-this-phrase–out of stock or disabled or incomplete
select p.sku, round(amazon_special_price_gbp,2) as price
, round(amazon_gbp_minimum_price,2) as [minimum-seller-allowed-price]
, round(amazon_list_price_gbp,2) as [maximum-seller-allowed-price], 0 as quantity, 4 as [handling-time]
, ‘default’ as [fulfillment-channel]
from price_calculations_jul_17_2018 p
inner join UkAllListingsReport07182018 uk on uk.[seller-sku]=p.sku
where /*(uk.status=’Active’) and*/ (p.status=2)
remove-this-phrase–not in our current inventory
select uk.[seller-sku] as sku, 0 as quantity, uk.price, uk.asin1 as asin, uk.[item-name]
from UkAllListingsReport07182018 uk remove-this-phrase–price_calculations_jul_17_2018 p
where uk.[seller-sku] not in (select sku from price_calculations_jul_17_2018)
remove-this-phrase–not in amazon remove-this-phrase– be careful to upload as not allowed product can be here
remove-this-phrase–better match with shopforsoul database for product ASIN to get a clear picture
select p.product_ean, p.sku, round(amazon_special_price_gbp,2) as price
, round(amazon_gbp_minimum_price,2) as [minimum-seller-allowed-price]
, round(amazon_list_price_gbp,2) as [maximum-seller-allowed-price]
,p.name as [item-name]
, 1 as quantity, 4 as [handling-time]
, ‘default’ as [fulfillment-channel]
from price_calculations_jul_17_2018 p
where p.status=1
and p.sku not in (select [seller-sku] from UkAllListingsReport07182018)
and p.[name] not like ‘%tv box%’
and p.[name] not like ‘%lenovo%’
and p.[name] not like ‘%teclast%’
and p.[name] not like ‘%huawei%’
remove-this-phrase–right join UkAllListingsReport07182018 uk on uk.[seller-sku]=p.sku
remove-this-phrase–where (uk.status=’Active’) and (p.status=2)
END
GO
*** . *** *** . *** . *** . ***
Training Courses: http://Training.SitesTree.com
Blog: http://Bangla.SaLearningSchool.com, http://SitesTree.com
8112223 Canada Inc/Justetc: http://JustEtc.net
Youtube: https://www.youtube.com/channel/UCHrtq0pV8mEuTLGd2-Lp9ig/playlists
Medium: https://medium.com/@SayedAhmedCanada
Sayed Ahmed
BSc. Eng. in Comp. Sc. & Eng. (BUET)
MSc. in Comp. Sc. (U of Manitoba, Canada)
MSc. in Data Science and Analytics (Ryerson University, Canada)
Linkedin: https://ca.linkedin.com/in/sayedjustetc
Facebook Groups/Forums to discuss (Q & A):
https://www.facebook.com/salearningschool
https://www.facebook.com/banglasalearningschool
https://www.facebook.com/justetcsocial
Get access to courses on Big Data, Data Science, AI, Cloud, Linux, System Admin, Web Development and Misc. related. Also, create your own course to sell to others. http://sitestree.com/training/
Join, Discuss, and Thrive:
Finance: https://www.facebook.com/groups/1792339521056034
Bangladesh and Canada: https://www.facebook.com/groups/398414183875009
Bangladesh: IT & Eng: Canada: https://www.facebook.com/groups/480297675634630
BUETians in Ontario: https://www.facebook.com/groups/371982239854027
Worldwide IT: https://www.facebook.com/groups/28362061867
JustEtc: https://www.facebook.com/JustETC-Technologies-Inc-289864738261
