-- 
-- Marge per Product
-- 
SELECT
	sa.ShopArticle_ID,
	sa.ShopArticle_Articlenumber,
	swp.ShopWAWIProduct_EAN,
	swp.ShopWAWIProduct_Listenpreis,
	IF(sp.ShopPrice_Price < swp.ShopWAWIProduct_Listenpreis, sp.ShopPrice_Price, swp.ShopWAWIProduct_Listenpreis) AS price,
	swp.ShopWAWIProduct_Einkaufspreis,
	ROUND((IF(sp.ShopPrice_Price < swp.ShopWAWIProduct_Listenpreis, sp.ShopPrice_Price, swp.ShopWAWIProduct_Listenpreis) - swp.ShopWAWIProduct_Einkaufspreis) / swp.ShopWAWIProduct_Einkaufspreis * 100.0, 2) AS marge
FROM ShopArticleItem AS sai
INNER JOIN ShopArticle AS sa ON (sa.ShopArticle_ID = sai.ShopArticle_ID)
INNER JOIN ShopWAWIProduct AS swp ON (swp.ShopWAWIProduct_ID = sai.ShopWAWIProduct_ID)
LEFT JOIN ShopPrice AS sp ON (sp.ShopArticleItem_ID = sai.ShopArticleItem_ID AND sp.ShopPriceVariant_Code = 'specialprice')


-- 
-- Total average marge
-- 
SELECT
	ROUND(AVG((IF(sp.ShopPrice_Price < swp.ShopWAWIProduct_Listenpreis, sp.ShopPrice_Price, swp.ShopWAWIProduct_Listenpreis) - swp.ShopWAWIProduct_Einkaufspreis) / swp.ShopWAWIProduct_Einkaufspreis * 100.0), 2) AS avgMarge
FROM ShopArticleItem AS sai
INNER JOIN ShopArticle AS sa ON (sa.ShopArticle_ID = sai.ShopArticle_ID)
INNER JOIN ShopWAWIProduct AS swp ON (swp.ShopWAWIProduct_ID = sai.ShopWAWIProduct_ID)
LEFT JOIN ShopPrice AS sp ON (sp.ShopArticleItem_ID = sai.ShopArticleItem_ID AND sp.ShopPriceVariant_Code = 'specialprice')

