/****** Object: StoredProcedure [dbo].[update_supplier_sku] 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].[update_supplier_sku]
AS
BEGIN
update [DealWithAmazonShopForSoul].[dbo].[supplier-dec-28-2018]
set Model_Code=concat(‘shopforsoul-‘, Model_Code)
where Model_Code not like ‘%shopfor%’;
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

USE [AmazonShopForSoul]
GO
/****** Object: StoredProcedure [dbo].[update_sku_once_for_price_calculations] 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].[update_sku_once_for_price_calculations]
AS
BEGIN
select * from price_calculations
where sku not like ‘%shopfor%’;

update price_calculations
set sku=concat(‘shopforsoul-‘, sku)
where sku not like ‘%shopfor%’;

select * from price_calculations;
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

USE [AmazonShopForSoul]
GO
/****** Object: StoredProcedure [dbo].[update_sku_once] 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].[update_sku_once]
AS
BEGIN
select * from all_to_upload_without_description
where sku not like ‘%shopfor%’;

update price_calculations_jul_17_2018
set sku=concat(‘shopforsoul-‘, sku)
where sku not like ‘%shopfor%’;

select * from all_to_upload_without_description;
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

USE [AdjustInActiveInventory]
GO
/****** Object: StoredProcedure [dbo].[update_inactive_listings_uk] Script Date: 2020-06-19 10:18:38 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].[update_inactive_listings_uk]
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, uk.asin1 as [product-id], uk.[product-id-type]
,p.amazon_special_price_gbp as price, p.amazon_gbp_minimum_price as [minimum-seller-allowed-price], p.amazon_list_price_gbp as [maximum-seller-allowed-price]
, uk.[item-condition], 2 as quantity, ‘a’ as [add-delete], 2 as [handling-time]
remove-this-phrase–, ‘default’ as [fulfillment-channel]
remove-this-phrase–,p.status, uk., uk.*
from UkAllListingsReport07092018 uk
inner join AllProductsPriceCalculation p on trim(p.sku) = trim(uk.[seller-sku])
where (uk. = ‘Inactive’) and (p.status = 1) and (uk.[item-name] not like ‘%tv%’ )
order by uk. desc

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

USE [AdjustInActiveInventory]
GO
/****** Object: StoredProcedure [dbo].[update_inactive_listings_eu] Script Date: 2020-06-19 10:18:38 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].[update_inactive_listings_eu]
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, de.asin1 as [product-id], de.[product-id-type]
,p.amazon_special_price_eu as price, p.amazon_eu_minimum_price as [minimum-seller-allowed-price], p.amazon_list_price_eu as [maximum-seller-allowed-price]
, de.[item-condition], 2 as quantity, ‘a’ as [add-delete], 2 as [handling-time]
remove-this-phrase–, ‘default’ as [fulfillment-channel]
remove-this-phrase–,p.status, de., de.*
from GermanyAllListingsReport07092018 de
inner join AllProductsPriceCalculation p on trim(p.sku) = trim(de.[seller-sku])
where (de. = ‘Inactive’) and (p.status = 1) and (de.[item-name] not like ‘%tv%’ )
order by de. desc

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

USE [AmazonProfitLossOrderManagement]
GO
/****** Object: StoredProcedure [dbo].[update_exchange_rate_and_price] Script Date: 2020-06-19 10:17:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
remove-this-phrase– Alter Procedure update_exchange_rate_and_price
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].[update_exchange_rate_and_price]
@exchange_rate_from_eu float, @exchange_rate_from_gbp float
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 * from [AmazonProfitLossOrderManagement].[dbo].[order_and_profit_loss_mgmt_july_15th_2018]

Select * from [AmazonProfitLossOrderManagement].[dbo].[order_and_profit_loss_mgmt_july_15th_2018]
where [sales-channel] not like ‘%.uk%’

update
[AmazonProfitLossOrderManagement].[dbo].[order_and_profit_loss_mgmt_july_15th_2018]
set exchange_rate=1.53673
where [sales-channel] not like ‘%.uk%’

Select * from [AmazonProfitLossOrderManagement].[dbo].[order_and_profit_loss_mgmt_july_15th_2018]
where [sales-channel] like ‘%.uk%’;

update
[AmazonProfitLossOrderManagement].[dbo].[order_and_profit_loss_mgmt_july_15th_2018]
set exchange_rate=1.74055
where [sales-channel] like ‘%.uk%’

remove-this-phrase–update canadian price
update
[AmazonProfitLossOrderManagement].[dbo].[order_and_profit_loss_mgmt_july_15th_2018]
set [item-price-cad]=[item-price]*exchange_rate

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

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

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

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

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