Solved

Magento 1.9.2 SQL Query to retrive buyers from our customers

Posted on 2016-08-03
6
67 Views
Last Modified: 2016-08-08
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!
0
Comment
Question by:apocalip30
  • 3
  • 2
6 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 41740742
SELECT * FROM Customers 
WHERE purchase_date BETWEEN 'start_date' AND 'end_date'

Open in new window

0
 
LVL 4

Expert Comment

by:Branislav Borojevic
ID: 41741420
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.
0
 

Author Comment

by:apocalip30
ID: 41742987
Yes it worked fine, thanks !

 ¿could we include number of order per customer?
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 4

Accepted Solution

by:
Branislav Borojevic earned 500 total points
ID: 41744545
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.
0
 

Author Comment

by:apocalip30
ID: 41747002
great thanks!
0
 

Author Closing Comment

by:apocalip30
ID: 41747309
thks
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now