Last week a client who runs his store on Magento Commerce asked me how he could export a list of customers who purchased between certain dates in August. Of course, I first went to the Administration area and tried several approaches (order reports, customer reports, data export). Unfortunately nothing worked, so I had to write a script or a plain SQL and run it from the CLI. I preferred a straight query, rather than a script, and I tried not to use sub-selects. Here’s the adapted part, where I’ve added some variables to keep all the attribute id’s:

SET @etID := (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'order_address');
SET @atFn := (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'firstname' AND entity_type_id = @etID);
SET @atLn := (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'lastname' AND entity_type_id = @etID);
SET @atEmail := (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'customer_email');

SET @startDate := '2010-08-06 00:00:00';
SET @endDate := '2010-08-13 23:59:59';

SELECT o.increment_id, e.value as email, ln.value as lastname, fn.value as firstname
FROM sales_order o
INNER JOIN sales_order_varchar e ON e.entity_id = o.entity_id AND attribute_id = @atEmail
INNER JOIN sales_order_entity lne ON lne.parent_id = o.entity_id AND lne.entity_type_id = @etID
INNER JOIN sales_order_entity_varchar ln ON ln.entity_id = lne.entity_id AND ln.attribute_id = @atLn
INNER JOIN sales_order_entity_varchar fn ON fn.entity_id = lne.entity_id AND fn.attribute_id = @atFn
WHERE o.created_at BETWEEN @startDate AND @endDate
GROUP BY o.increment_id
ORDER BY o.increment_id DESC;

The only problem is when the Shipping Name is different than the Billing Name – there’s no control over that, as we’re grouping by increment_id. This could easily be fixed in a PHP script, but the approach there should be different (using collections).

Hope this helps anyone, as it did the trick for me.

Solution tested under Magento 1.4.0.1

Cristi Magento, MySQL Magento, sql