I have a Magento 1.9 site and I am trying to write a sql query that would return a listing of all skus (disabled and enabled) in the site as well as how many of each sku was sold between a specific date range and then also show how many qty are currently on-hand still for the item.
My issue is I have the below query however it only lists the data I need of the items that were sold between x and x date. The below query is not listing all items even items that were not sold between the stated dates.
I am not sure how to change the query. I need it all as one query since I am exporting the result to excel
//form post variables $fromdate = mysqli_real_escape_string($cn, $_POST['volumefrom']); $todate = mysqli_real_escape_string($cn, $_POST['volumeto']); $list = mysqli_query($cn, "SELECT sales_flat_order_item.name, sales_flat_order_item.sku, sum(sales_flat_order_item.qty_ordered) As Qty_Sold, cataloginventory_stock_item.qty AS On_Hand FROM sales_flat_order_item, catalog_product_entity_varchar, catalog_product_entity, cataloginventory_stock_item WHERE catalog_product_entity_varchar.attribute_id = 71 AND catalog_product_entity_varchar.entity_id = cataloginventory_stock_item.product_id AND catalog_product_entity_varchar.entity_id = catalog_product_entity.entity_id AND sales_flat_order_item.sku = catalog_product_entity.sku AND sales_flat_order_item.price > 0 AND sales_flat_order_item.created_at >= '$fromdate 00:00:00' AND sales_flat_order_item.created_at <= '$todate 23:59:59' GROUP BY sales_flat_order_item.sku ORDER BY sales_flat_order_item.sku LIMIT 1000");