Magento ERP sell-through report

sell-through-report.sql

SELECT brand, name, style, color, size, season, initial_qty, current_qty, sell_through, price, avg_price, cost, avg_cost FROM (
	SELECT 
		initial_qty.sm_product_id, brand, name, style, color, size, season, initial_qty.qty AS initial_qty, current_qty.qty AS current_qty, ROUND(IF(initial_qty.qty < current_qty.qty, 0, current_qty.qty / initial_qty.qty), 2) AS sell_through, ROUND(price,2) AS price, ROUND(cost,2) AS cost,
		(SELECT ROUND(AVG(price),2) FROM sales_flat_order_item WHERE price != 0 AND sku = initial_qty.sku) AS avg_price,
		(SELECT ROUND(AVG((pop_price_ht * (IF(pop_discount > 0, pop_discount, 100) / 100))),2) FROM purchase_order_product WHERE pop_product_id = initial_qty.sm_product_id) AS avg_cost
	FROM (
		SELECT a.sm_product_id, b.sku, b.manufacturer_value AS brand, b.name, b.vendor_product_id AS style, b.image_label AS color, b.choose_size_value AS size, b.season, SUM(IF(sm_source_stock = 1, -sm_qty, sm_qty)) AS qty, price, cost
		FROM stock_movement AS a
		INNER JOIN catalog_product_flat_1 AS b ON a.sm_product_id = b.entity_id
		WHERE (sm_source_stock = 1 OR sm_target_stock = 1)
		AND CAST(sm_date AS DATE ) <= '2012-08-17'
		GROUP BY sm_product_id
	) AS initial_qty
	INNER JOIN (
		SELECT a.sm_product_id, SUM(IF(sm_source_stock = 1, -sm_qty, sm_qty)) AS qty
		FROM stock_movement AS a
		WHERE (sm_source_stock = 1 OR sm_target_stock = 1)
		AND CAST(sm_date AS DATE ) <= '2012-08-22'
		GROUP BY sm_product_id
	) AS current_qty
	ON initial_qty.sm_product_id = current_qty.sm_product_id
) AS x