Solved

Magento 1.9.2 SQL Query to retrive buyers from our customers

Posted on 2016-08-03
6
122 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
selecting date modified field from a table 2 52
unable to insert record into a table 2 37
Complex MySQL Query 2 33
Combining Queries 7 27
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
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…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
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…

856 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