We help IT Professionals succeed at work.

Magento 1.9.2 SQL Query to retrive buyers from our customers

apocalip30
apocalip30 asked
on
We need to retrive form our database all the buyers form our customers during a range of dates.

That is all the customers that bought something duting a range of dates, we are using Magento 1.9.2

Thank you!
Comment
Watch Question

SharathData Engineer

Commented:
SELECT * FROM Customers 
WHERE purchase_date BETWEEN 'start_date' AND 'end_date'

Open in new window

SELECT `e`.*, MAX(o.created_at) AS `last_order_date` FROM `customer_entity` AS `e` LEFT JOIN `sales_flat_order` AS `o` ON o.customer_id = e.entity_id WHERE (`e`.`entity_type_id` = '1') GROUP BY `e`.`entity_id` HAVING (last_order_date < '2014-03-26') OR (last_order_date IS NOT NULL)

Open in new window


This will return all customers that made a purchase from the last_order_date (you can set your own date) and the date when you make the query.

Hope it helps.

I tested it myself, it should work and return what you need.

Let me know if it works for you.

Author

Commented:
Yes it worked fine, thanks !

 ¿could we include number of order per customer?
SELECT customer_email, customer_firstname, customer_lastname, SUM(subtotal_invoiced) AS total_of_orders, MIN(created_at) as first_order_date, MAX(created_at) as last_order_date, COUNT(*) as number_of_orders FROM sales_flat_order GROUP BY customer_email

Open in new window


The following code will show you Customer Email, First and Last Name, Total of Orders for that customer, when they made the first order, and when they made the last order.

Author

Commented:
great thanks!

Author

Commented:
thks