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

Question

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!

Answer 1

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.

Answer 2

Below query will help you:- You can implement where clause as per your requirement.

SELECT increment_id AS `Order Id` , address.region AS `state` , address.postcode AS `zipcode` , order.base_subtotal_incl_tax AS `tax` FROM sales_flat_order `order` JOIN sales_flat_order_address `address` ON order.entity_id = address.parent_id