Jun 19

test

USE [AmazonProfitLossOrderManagement]
GO
/****** Object: StoredProcedure [dbo].[test] Script Date: 2020-06-19 10:17:11 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].[test]
AS
BEGIN
/****** Script for SelectTopNRows command from SSMS ******/

SELECT ama.*remove-this-phrase–, c.*
FROM [AmazonProfitLossOrderManagement].[dbo].[order_and_profit_loss_mgmt_july_15th_2018] ama
where [purchase-date] > ‘2018-04-01’ and [purchase-date] < ‘2018-07-01′
and [order-id]=’304-2877510-2797936’

SELECT ama.*, c.*
FROM [AmazonProfitLossOrderManagement].[dbo].[order_and_profit_loss_mgmt_july_15th_2018] ama
inner join costdata c on c.[order-id] = ama.[order-id] and (c.[sales-channel] = ama.[sales-channel])
remove-this-phrase—
SELECT ama.*, c.*
FROM [AmazonProfitLossOrderManagement].[dbo].[order_and_profit_loss_mgmt_july_15th_2018] ama
inner join costdata c on c.[order-id] <> ama.[order-id] remove-this-phrase–and (c.[sales-channel] = ama.[sales-channel])
remove-this-phrase—
SELECT *
FROM costdata c
where c.[order-id] not in ( select [order-id] from [order_and_profit_loss_mgmt_july_15th_2018])

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

Jun 19

sync_with_uk_inventory

/****** Object: StoredProcedure [dbo].[sync_with_uk_inventory] Script Date: 2020-06-19 10:12:46 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].[sync_with_uk_inventory]
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;

delete from [uk-ActiveListingsReport-06-17-2018] where [uk-ActiveListingsReport-06-17-2018].quantity=0

remove-this-phrase–Germany active not in uk
remove-this-phrase–deactivate them
select trim(ge.seller_sku)
from [germany-ActiveListingsReport-06-17-2018] ge
where (
trim(ge.seller_sku) not in (select trim(uk.seller_sku)
from [uk-ActiveListingsReport-06-17-2018] uk )
)
remove-this-phrase–

select trim(ge.seller_sku) as sku, ge.asin1 as asin, ge.quantity, ge.price
into #GermanyActiveProducts
from [germany-ActiveListingsReport-06-17-2018] ge

select trim(uk.seller_sku) as sku, uk.asin1 as asin, uk.quantity, uk.price
into #UKActiveProducts
from [uk-ActiveListingsReport-06-17-2018] uk

select * from #UKActiveProducts

select * from
#GermanyActiveProducts ge
where ge.sku not in (select sku from #UKActiveProducts)

select uk.* from
#UKActiveProducts uk
where uk.sku not in (select sku from #GermanyActiveProducts)

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

Jun 19

product_name_mismatch

USE [AmazonInventory-2018-06-14]
GO
/****** Object: StoredProcedure [dbo].[product_name_mismatch] Script Date: 2020-06-19 10:18:12 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].[product_name_mismatch]
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;

/****** Script for SelectTopNRows command from SSMS ******/
SELECT ama.asin1, ama.[seller-sku], supplier.sku, ama.[item-name] as name_on_amazon, supplier.name as name_at_supplier_side
FROM [ActiveListingsReport-06-19-2018] ama
inner join [all-to-upload-without-description] supplier on trim(ama.[seller-sku])=concat(‘shopforsoul-‘,supplier.sku)
where ama.[item-name] <> supplier.name
order by ama.[item-name]

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

Jun 19

our_profit_loss

USE [AmazonProfitLossOrderManagement]
GO
/****** Object: StoredProcedure [dbo].[our_profit_loss] Script Date: 2020-06-19 10:17:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
remove-this-phrase– Alter Procedure our_profit_loss
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].[our_profit_loss]
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;

/****** Script for SelectTopNRows command from SSMS ******/
declare @customer_paid float;
declare @ur_total_cost float;

SELECT @customer_paid=([item-price-cad] + [item-tax-cad] + [shipping-price-cad] + [shipping-tax-cad] ), @ur_total_cost = ([our_purchase_price] + [our_shipping_price] + our_shipping_insurance)
FROM [AmazonProfitLossOrderManagement].[dbo].[order_and_profit_loss_mgmt_july_15th_2018]

select @customer_paid, @ur_total_cost

update [order_and_profit_loss_mgmt_july_15th_2018]
set total_customer_pay=([item-price-cad] + [item-tax-cad] + [shipping-price-cad] + [shipping-tax-cad] )

update [order_and_profit_loss_mgmt_july_15th_2018]
set our_total_cost=([our_purchase_price] + [our_shipping_price] + our_shipping_insurance)

update [order_and_profit_loss_mgmt_july_15th_2018]
set our_profit_loss=round(total_customer_pay-our_total_cost-amazon_fees_cad,2)

select * from [order_and_profit_loss_mgmt_july_15th_2018]

update [order_and_profit_loss_mgmt_july_15th_2018]
set our_purchase_price=0 where our_purchase_price is null

update [order_and_profit_loss_mgmt_july_15th_2018]
set our_shipping_price=0 where our_shipping_price is null

update [order_and_profit_loss_mgmt_july_15th_2018]
set our_shipping_insurance=0 where our_shipping_insurance is null

update [order_and_profit_loss_mgmt_july_15th_2018]
set refund_to_customer=0 where refund_to_customer is null

select * from [order_and_profit_loss_mgmt_july_15th_2018]
order by [purchase-date] desc

update [order_and_profit_loss_mgmt_july_15th_2018]
set order_cancelled=0
where order_cancelled is null

select sum(our_profit_loss) as proft, sum(our_profit_loss)*0.15 as possible_tax, sum(our_profit_loss)*0.15 as possible_vat,
(sum(our_profit_loss) – sum(our_profit_loss)*0.15) as actual_profit
from [order_and_profit_loss_mgmt_july_15th_2018]
where order_cancelled=0

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

Jun 19

minimum_price_for_amazon

USE [AmazonMinimumPriceAdjustment]
GO
/****** Object: StoredProcedure [dbo].[minimum_price_for_amazon] Script Date: 2020-06-19 10:17:46 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].[minimum_price_for_amazon]
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 ours.*
FROM [our_canadian_prices_for_minimum-maximum_template] ours
inner join [canada-Inventory+Report+07-02-2018] canada on canada.sku = ours.sku

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

Jun 19

generate_price_calculation_data

/****** Object: StoredProcedure [dbo].[generate_price_calculation_data] Script Date: 2020-06-19 10:15:42 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].[generate_price_calculation_data]
AS
BEGIN
remove-this-phrase– Visibility status
SELECT [Product ID] as product_id
,[Model Code] as sku
,[Full Product Name] as [Name]
, as _category
, ‘All’ as _root_category
,[1pc Price] as raw_price
,[1pc Cheapest Postal Shipping Price] as dummy
,[1pc Cheapest Express Courier Shipping Price] as cheapest_courier_shipping
,[EAN] as product_ean
remove-this-phrase–Ship Risk Price Risk Total Risk Return Total with ship Referral Fee Paypal Fees Final Value Fee Insertion Fee HST EU Vat Currency Risk shopforsoul_profit ebay_profit Income Tax shopforsoul_cost MLP Special List Price special_from_date special_to_date ebay_cost ebay_list_price Buyer pay from Mall Eay Buyers Pay Ebay vs Mall NA-1 Amazon Final Value Fee amazon_profit amazon_our_cost amazon_list_price amazon_special_price Amazon Final Value Fee USD amazon_profit_usd amazon_our_cost_usd amazon_list_price_usd amazon_special_price_usd amazon_list_price_gbp amazon_special_price_gbp amazon_list_price_eu amazon_special_price_eu amazon_list_price_mxn amazon_special_price_mxn ama_safe_refund_amount
,ROUND([1pc Cheapest Express Courier Shipping Price]*0.03,2) as ship_risk
,ROUND([1pc Price]*0.03,2) as price_risk
remove-this-phrase–,ROUND([1pc Price]+G2+I2+J2,2) as total
,ROUND([1pc Cheapest Express Courier Shipping Price]*0.2,2) as risk_return
remove-this-phrase–,ROUND(K2+L2;2) as total_with_shipping
remove-this-phrase–,ROUND(M2*0.1;2) as referral_fee
remove-this-phrase–,ROUND(0.3+M2*0.03;2) as paypal_fees
remove-this-phrase–,ROUND(M2*0.07;2) as final_value_fees
remove-this-phrase–,ROUND(M2*0.01;2) as insertion_fees
remove-this-phrase–,ROUND(M2*0.13;2) as HST
remove-this-phrase–,ROUND(M2*0.25;2) as eu_vat
remove-this-phrase– amazon_ca_minimum_price amazon_usd_minimum_price amazon_gbp_minimum_price amazon_eu_minimum_price amazon_mxn_minimum amazon_ca_abs_minimum expedited_cost
,[Soon Discontinued]
,[Weight Kg] * 2.2 as Weight
,[Height mm] as Height
,[Width mm] as Width
,[Depth mm] as Depth
,[Retail Price] as Retail_price
,[Stock status] as "Stock status"
,[Date Back]
remove-this-phrase–Weight right Ship Right Is Normal
FROM [DealWithAmazonShopForSoul].[dbo].[supplier-data-jan-01-2019]
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

Jun 19

auto_pricing_data_for_usa

USE [AmazonShopForSoul]
GO
/****** Object: StoredProcedure [dbo].[auto_pricing_data_for_usa] 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_usa]
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, round(amazon_special_price_usd,2) as price
, round(amazon_usd_minimum_price,2) as [minimum-seller-allowed-price]
, round(amazon_list_price_usd,2) as [maximum-seller-allowed-price], 1 as quantity
, 3 as [handling-time], ‘default’ as [fulfillment-channel]
from price_calculations_jul_17_2018 p
inner join UsaAllListingsReport07182018 usa on usa.[seller_sku]=p.sku
where (usa.status=’Active’) and (p.status=1)

remove-this-phrase–out of stock or disabled
select p.sku, round(amazon_special_price_usd,2) as price
, round(amazon_usd_minimum_price,2) as [minimum-seller-allowed-price]
, round(amazon_list_price_usd,2) as [maximum-seller-allowed-price], 0 as quantity
, 3 as [handling-time], ‘default’ as [fulfillment-channel]
from price_calculations_jul_17_2018 p
inner join UsaAllListingsReport07182018 usa on usa.[seller_sku]=p.sku
where (usa.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

Jun 19

auto_pricing_data_for_united_kingdom

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

Jun 19

auto_pricing_data_for_spain

USE [AmazonShopForSoul]
GO
/****** Object: StoredProcedure [dbo].[auto_pricing_data_for_spain] 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_spain]
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_eu as price, amazon_eu_minimum_price as [minimum-seller-allowed-price], amazon_list_price_eu as [maximum-seller-allowed-price], 1 as quantity, 2 as [handling-time], ‘default’ as [fulfillment-channel]
from price_calculations p
inner join [spain-Inventory+Report+07-03-2018] spain on spain.sku=p.sku
*/

remove-this-phrase–On July 13th, 2018
remove-this-phrase–no longer required
/*
select p.sku, amazon_special_price_eu as price, amazon_eu_minimum_price as [minimum-seller-allowed-price], amazon_list_price_eu 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 [SpainInventoryReport07132018] spain on spain.sku=p.sku
*/

/*fix null price*/
select * from SpainAllListingsReport07182018
where price is null

update
SpainAllListingsReport07182018
set price=0.02
where price is null

remove-this-phrase–product that should be on amazon
select p.sku, REPLACE (round(amazon_special_price_eu,2), ‘.’, ‘,’) as price
, REPLACE (round(amazon_eu_minimum_price,2), ‘.’, ‘,’) as [minimum-seller-allowed-price]
, REPLACE (round(amazon_list_price_eu,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 SpainAllListingsReport07182018 eu on eu.[seller-sku]=p.sku
where (eu.status=’Active’) and (p.status=1)

remove-this-phrase–remove-this-phrase–out of stock or disabled or incomplete or soon discontinued
select p.sku, REPLACE (round(amazon_special_price_eu,2), ‘.’, ‘,’) as price
, REPLACE (round(amazon_eu_minimum_price,2), ‘.’, ‘,’) as [minimum-seller-allowed-price]
, REPLACE (round(amazon_list_price_eu,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 SpainAllListingsReport07182018 eu on eu.[seller-sku]=p.sku
where (p.status=2)

remove-this-phrase–not in our current supplier inventory
remove-this-phrase–but on our Amazon inventory
select eu.[seller-sku] as sku, 0 as quantity
, replace(eu.price,’.’,’,’) as price, eu.asin1 as asinremove-this-phrase–, eu.[item-name]
from SpainAllListingsReport07182018 eu remove-this-phrase–price_calculations_jul_17_2018 p
where eu.[seller-sku] not in (select sku from price_calculations_jul_17_2018)

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

Jun 19

auto_pricing_data_for_mexico

USE [AmazonShopForSoul]
GO
/****** Object: StoredProcedure [dbo].[auto_pricing_data_for_mexico] 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_mexico]
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, round(amazon_special_price_mxn,2) as price, round(amazon_mxn_minimum,2) as [minimum-seller-allowed-price]
, round(amazon_list_price_mxn,2) as [maximum-seller-allowed-price], 1 as quantity,
3 as [handling-time], ‘default’ as [fulfillment-channel]
from price_calculations_jul_17_2018 p
inner join MexicoAllListingsReport07182018 mexico on trim(mexico.seller_sku)=trim(p.sku)
where (mexico.status=’Active’) and (p.status=1)

remove-this-phrase—out of stock and/or disabled
select p.sku, round(amazon_special_price_mxn,2) as price, round(amazon_mxn_minimum,2) as [minimum-seller-allowed-price]
, round(amazon_list_price_mxn,2) as [maximum-seller-allowed-price], 0 as quantity,
3 as [handling-time], ‘default’ as [fulfillment-channel]
from price_calculations_jul_17_2018 p
inner join MexicoAllListingsReport07182018 mexico on trim(mexico.seller_sku)=trim(p.sku)
where (mexico.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