Quick Magento MySQL Query for Tax Forms Recently one of our clients tried running the Tax Report in Magento under Reports -> Sales -> Tax for the last year. Unfortunately the company that had originally set up her site did not have her tax setting configured correctly! So as it turned out she had never collected tax for any of the orders in her State. Luckily thought we we’re able to come up with a quick MySQL query to run on her database that could extract these orders and their totals for us. Her store is located in Michigan so we need to pull out all the orders with the state abbreviation ”MI”. If you need to run this query on your own database simply replace the two “MI”s with your state’s abbrevation.
Here’s the query:
select s.entity_id as order_number, s.base_grand_total, s.shipping_amount, s.base_shipping_amount, s.base_subtotal, br.code as billing_state, ar.code as shipping_state from sales_flat_order s left join customer_address_entity_int b on s.billing_address_id = b.entity_id and 28 = b.attribute_id left join directory_country_region br on b.`value` = br.region_id left join customer_address_entity_int a on s.shipping_address_id = a.entity_id and 28 = a.attribute_id left join directory_country_region ar on a.`value` = ar.region_id where br.code = ‘MI’ or ar.code = ‘MI’
Note that this was done on Magento ver. 1.4.1.1, some modifications may be necessary for other version of Magento.