Querying total historical sales for customers who ordered within a specified time frame
Posted on 2011-05-10
In the orders table, I select all orders placed within a specific date range. Then, I get the customer numbers from that table. The distinct count of these customer numbers provides the total number of customers who placed orders at least once during that time frame.
Next, I want to find the total of all items ordered for each of these same customers from the complete history, regardless of date range, to determine the total of products bought by them over their entire customer lifetime. How can I accomplish this? In each of my attempts so far, all I’ve been able to gather are the total of items bought that fall in the initially specified date range for these customers. I’ve tried various joins, but to no avail.
In other words, how do I expand my reach in the detailed items table to gather data beyond the date bound orders found in the order header table?