[SOLVED] Get Shipping State, Zip, and Tax paid on Magento Orders in Date Range

Get Shipping State, Zip, and Tax paid on Magento Orders in Date Range

I need to export a list of all orders between dates X & Y that shows the following:

Order ID
State Shipping
Zip Shipped
Sales Tax Collected

Is there an easy query I can run to pull this information from the orders table?

The current X is January 1, 2015; the current Y is March 31, 2015.

I really only need orders shipped TO California (the only state we charge tax), but can filter this out through sorting the exported CSV list later.

Thank you!

Solution

You need two tables to get your data, here is the SQL :

SELECT a.increment_id AS 'Order ID', b.region AS 'State Shipping', b.postcode AS 'Zip Shipped', a.base_tax_amount AS 'Sales Tax Collected'
FROM sales_flat_order a
JOIN sales_flat_order_address b
ON a.entity_id = b.parent_id
WHERE a.created_at >= '2015-01-01 00:00:00' AND a.created_at <= '2015-03-31 23:59:59'
GROUP BY a.entity_id

few things need be care:

tax in sales_flat_order table has many fields, I am not sure this is what you looking for
the create_at value you might want to change. In my case, my Magento order created time value is faster 11 hours than my computer time, maybe the timezone issue.
the 'GROUP BY' is for get rid of duplicate rows after select the data from two tables.