SET @START = '{STARTDATE}', @END = '{ENDDATE}'; SELECT * FROM ( SELECT CONCAT(pr.REV_CATEGORY,':',pr.REVENUE_SUBCATEGORY) AS Account, ( SELECT vendors.VENDOR_NAME FROM vendors WHERE vendors.VENDOR_ID = pr.vendor_id ) AS Vendor, pr.description AS Description, pr.SIZE_DESC AS Size, pr.COLOR_DESC AS Color, IF(sales.QUANTITY IS NULL,0,sales.QUANTITY) AS Sales, qty.INVQTY Inventory, date( ( SELECT max(restock.postdate) FROM restock WHERE restock.product_id = pr.product_id ) ) AS Latest_Restock_Date FROM products pr INNER JOIN ( SELECT invqty.PRODUCT_ID, sum(invqty.QUANTITY) INVQTY FROM ( ( SELECT invoices.POSTDATE, invoice_items.PRODUCT_ID, invoice_items.QUANTITY*(-1) AS QUANTITY, 'SALE' AS Trans_Type FROM invoice_items Inner Join products ON products.PRODUCT_ID = invoice_items.PRODUCT_ID Inner Join invoices ON invoices.INVOICE_ID = invoice_items.INVOICE_ID WHERE invoices.VOIDEDINVOICE = 0 AND (products.PRODUCT_TYPE = 'ICHILD' OR products.PRODUCT_TYPE = 'ISINGLE') AND products.INACTIVE = 0 ) UNION ALL ( SELECT restock.POSTDATE, restock.PRODUCT_ID, restock.QUANTITY, restock.RTYPE AS Trans_Type FROM restock Inner Join products ON products.PRODUCT_ID = restock.PRODUCT_ID WHERE (products.PRODUCT_TYPE = 'ICHILD' OR products.PRODUCT_TYPE = 'ISINGLE') AND products.INACTIVE = 0 ) ORDER BY PRODUCT_ID ASC, POSTDATE ASC ) invqty GROUP BY invqty.PRODUCT_ID ) qty ON qty.PRODUCT_ID = pr.PRODUCT_ID LEFT JOIN ( SELECT ii.PRODUCT_ID, SUM(ii.QUANTITY) AS QUANTITY FROM invoice_items ii Inner Join products p ON p.PRODUCT_ID = ii.PRODUCT_ID Inner Join invoices i ON i.INVOICE_ID = ii.INVOICE_ID WHERE i.VOIDEDINVOICE = 0 AND (p.PRODUCT_TYPE = 'ICHILD' OR p.PRODUCT_TYPE = 'ISINGLE') AND p.INACTIVE = 0 AND i.POSTDATE BETWEEN '2017-01-01' AND '2017-01-31' GROUP BY ii.PRODUCT_ID ) sales ON sales.PRODUCT_ID = pr.PRODUCT_ID WHERE PRODUCT_TYPE IN ('ICHILD', 'ISINGLE') AND INACTIVE = 0 ORDER BY VENDOR_ID, pr.DESCRIPTION ) output WHERE Inventory <> 0 AND SALES = 0 AND DATE(Latest_Restock_Date) < DATE(@START) ORDER BY Account ASC, Vendor ASC, Description ASC, Size ASC, Color ASC