[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Querying total historical sales for customers who ordered within a specified time frame

Posted on 2011-05-10
26
Medium Priority
?
282 Views
Last Modified: 2012-06-27
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?
0
Comment
Question by:makingitbig
  • 12
  • 7
  • 4
  • +1
26 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 35733263
try like this.
select t1.CustomerID,COUNT(*) TotalCount
  from Orders as t1
 where exists (select 1 from Orders as t2 where t1.CustomerID = t2.CustomerID and t2.OrderDate between @Date1 and @Date2 )
 group by t1.CustomerID

Open in new window

0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35733289
Are you trying to do this in the report or in SQL?

mlmcc
0
 

Author Comment

by:makingitbig
ID: 35734349
To answer the question from mlmcc: I'm trying to do this from within Crystal Reports.

In response to Sharath 123: Thanks - I'll have to study it to understand it and think about how to translate that to my specific situation and see if it works.  I'll respond accordingly.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:makingitbig
ID: 35734471
Sharath 123:  I tried your solution, but that just gets me back to where I was, a count of the customers who placed orders within a date range (for instance, 964 customers ordered between 2011-05-01 and 2011-05-09.

Here's my implementation of your suggestion using my table.column names:

select CMS.Custnum,COUNT(*) TotalCount
 from CMS as CMS
 where exists(select 1 from CMS as Items where CMS.ORDERNO = Items.ORDERNO and Items.odr_date between '2011-05-01' and '2011-05-09') 
 group by CMS.CUSTNUM

Open in new window



What I need beyond that, is a total ordered by each of these 964 customers from Day 1 (which goes back to 2002) up through and including the specified date range.  I am trying to determine the 'lifetime value' of customers who ordered in specified date ranges.

In other words, the total of Items.Price * Items.Quantity for each of the 964 customers since 2002.

So, more offers of suggestions are welcome and greatly appreciated!

0
 

Author Comment

by:makingitbig
ID: 35734482
also: the above I did in SQL Server Manager.  Once this problem is solved, I have to port this to Crystal Reports, perhaps directly or perhaps it would need to involve a stored procedure - I'll cross that bridge when it is reached.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35734715
WHat fields are in the table?

What do you need the report to show?

This can all be done in Crystal but the method depends on the available data and what you need the report to show.

mlmcc
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35735511
I don't see any problem with my query. Post some sample data where its not working.
0
 

Author Comment

by:makingitbig
ID: 35739133
The tables are:

CMS - the orders headers table and ITEMS - the line item details table.

The linking fields are:

CMS.ORDERNO to ITEMS.ORDERNO

But, the problem is that I need more than this.  I need to total all of the history of details in the ITEMS table (specifically two fields: ITEMS.QUANTO and ITEMS.UN_LIST for each line item, which, when multiplied together, give the total dollars spent on that line item).  This part is not particularly difficult, the hard part is this: once an order has been found within a specified date range, I then need to find all orders for that customer number and get the total of all ITEMS.QUANTO * ITEMS.UN_LIST from ITEMS table, regardless of order date.

So, the other field from the CMS table that is critical is CMS.CUSTNUM.  Once I know this number, I need to pull all headers from CMS that have the same CUSTNUM, then get each of those ORDERNO and then go into ITEMS, using ORDERNO as the linking field, to pull all history for that particular customer.  In other words, I need to determine all customers that ordered within a particular date range.  After that, I need to compute the total of all of the order history from the beginning of the ITEMS table.

So, if Bob ordered something on May 9th, 2011, what is the total value of everything he has ordered from me since he first ordered back in 2002?  And, how many orders?  
0
 

Author Comment

by:makingitbig
ID: 35739157
To Sharath 123:

Here is the output from the code I wrote based upon your suggestion.  As you can see, it shows the customer number and the number of times they've ordered, but only the number of times within the specified date range.  I need it for all time (see my note above in response to mlmcc)

<sample deleted from question and attached as file>
<mlmcc>
Q-27030030.txt
0
 

Author Comment

by:makingitbig
ID: 35739183
oops - sorry about that; I meant to cut that off after a dozen, or so, but got in a hurry and forgot!!  (can I edit it to clean it up?)
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35739220
I can delete it and you can add it back as a text file

mlmcc
0
 

Author Comment

by:makingitbig
ID: 35739790
will do; thanks.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35741050
Post some sample data from two tables. I don;t understand what's going wrong. This is how I tested.
declare @Orders table (CustomerID int,OrderDate datetime)
insert @Orders values (1,'08/08/2010'),(1,'09/08/2010'),(1,'08/08/2009')
insert @Orders values (2,'08/08/2009'),(2,'09/08/2008'),(2,'08/08/2007')
insert @Orders values (3,'04/10/2010'),(3,'03/12/2010'),(3,'02/16/2007'),(3,'02/16/2006')
declare @Date1 datetime,@Date2 datetime
select @Date1 = '01/01/2010',@Date2 = '12/31/2010'
select t1.CustomerID,COUNT(*) TotalCount
  from @Orders as t1
 where exists (select 1 from @Orders as t2 where t1.CustomerID = t2.CustomerID and t2.OrderDate between @Date1 and @Date2 )
 group by t1.CustomerID
/*
CustomerID	TotalCount
1	3
3	4
*/

Open in new window

In my sample data, Customer 1 and 3 have orders in the range of 01/01/2010 and 12/31/2010. The total count of orders for Customer 1 and 3 are 3 and 4 respectively.
0
 

Author Comment

by:makingitbig
ID: 35741304
Yes, I see that, but that's not what I'm looking for.  See my earlier posts for a complete description of the problem.
0
 

Author Comment

by:makingitbig
ID: 35741381
see my post  ID: 35739133 which was a reply to mlmcc  (above) for the best description of the challenge.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35741517
How can I determine the total of the orders?

What is the report supposed to look like?

Do you need details or just totals?

mlmcc
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35741716
I need some sample data from two tables with expected result.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35741735
Try this to get the totals

SELECT SalesTable.Customer, Sum([SalesTable]![ProductQty]*[SalesTable]![ProductPrice]) AS TotalSales
FROM SalesTable, subQry
GROUP BY SalesTable.Customer
HAVING (((SalesTable.Customer) In (SELECT subQry.Customer from subQry)));

If you prefer to use the report to  do the calculations

SELECT SalesTable.Customer, SalesTable.ProductQty, SalesTable.ProductPrice
FROM SalesTable, subQry
WHERE SalesTable.Customer In (SELECT subQry.Customer from subQry);

mlmcc
0
 
LVL 35

Expert Comment

by:James0628
ID: 35753019
FWIW, if you go back to Sharath_123's original query, he referred to an Orders table, which was presumably supposed to have one row for each item.  In your version of that query you used CMS, which is the header table, so you presumably got a count of the orders (not items) for each customer.  You'd need to use the ITEMS table in the main query to get a count (or another summary) of the items for each customer.

 As for it only including the orders within your date range, that's because in the subquery you have

where CMS.ORDERNO = Items.ORDERNO

 So, the subquery only includes rows with the same order number as the current order number in the main query, that also fall in your date range, so you end up only including the orders within your date range.

 If you look back at Sharath_123's original query, you'll see that he was checking the customer (CustomerID) in the subquery.  The idea is for the subquery to only produce a result if the customer in the current row of the main query has any orders within the date range, but you've connected the main and subquery by the order instead, so the subquery only produces a result if the order in the current row of the main query is within the date range.

 I hope that made sense.  :-)  The upshot is that you need to check the customer column in the WHERE in the subquery, instead of the order column.

 James
0
 

Author Comment

by:makingitbig
ID: 35756774
I'm still not getting this.

Let's say I have the following two simple tables:

Table 1 - CMS
CMS.ORDERNO - common to ITEMS table
CMS.ORDERDATE
CMS.CUSTNUM

and

Table2 - ITEMS
ITEMS.ORDERNO - common to CMS table
ITEMS.ITEMNUM
ITEMS.QUANTITY
ITEMS.PRICE

1. I can determine the customers that ordered in a particular time frame by looking at the CMS.ORDERDATE and then grabbing the CMS.CUSTNUM.
2. Using those CMS.CUSTNUMs I now need to gather all of the CMS.ORDERNOs for each CMS.CUSTNUM.
3. Once I have all of the CMS.ORDERNOs, I then need to accumulate all sales from the ITEMS table, linking CMS.ORDERNO to ITEMS.ORDERNO for each CUSTNUM, grouped by CUSTNUM.

As such, it seems that this would have to include at least a three-pass process.  One for each step above.

Hopefully, either I am incorrect or this is 'doable' in such a way that I can incorporate it into a Crystal Report.   Ideally, this is simpler that it appears to me!
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 1000 total points
ID: 35757063
Try this

SELECT CMS.CustNum, Sum(([Items]![Qty]*[Items]![Price])) AS TotalSale
FROM CMS INNER JOIN Items ON CMS.OrderNo = Items.OrderNum
WHERE  CMS.CustNum IN (SELECT C1.CustNum FROM CMS C1 WHERE C1.OrderDate >= #1 Apr 11#)
GROUP BY CMS.Custnum

That works in Access with the tables designed as you specified.

mlmcc
0
 
LVL 35

Expert Comment

by:James0628
ID: 35759873
mlmcc's query should give you the total sales for the customers that bought in your date range, although the WHERE in the subquery should presumably be changed to compare OrderDate with your date range parameters (?), instead of just >= #1 Apr 11#.

 Do you also need data specifically from the orders that are in your date range, like a list of those orders or totals for them or something?  For example, if a customer had orders between 03/01 and 03/31, do you want to see a list of those orders or totals for them, as well as a total for _all_ of the orders that that customer has ever placed?  If so, the data for the orders in your date range would need to be added somewhere.

 James
0
 

Author Comment

by:makingitbig
ID: 35764552
mlmcc's solution works well - but another issue has arisen now that I'm running the query -

I realized that what I need to test for is if the most recent order was in the specified time range, not if any order exists in the time frame.

How can I limit or stop the search once I've determined this?

I'm thinking that I'll need to establish an While or If loop that exists once the condition is True, but not sure if this would be the recommended method or how to implement a conditional SELECT within the working query.

(Also: I've put my current work into a stored procedure which I'm linking to from Crystal Reports.  Would this be the recommend way, or should/can I put the SQL code directly into CR XI?  If so, how, as I can see queries in CR XI, but can't seem where to edit it?
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35764765
You can't edit a query iin Crystal unless you use a command for the data source.

Whenn you create/add the data source one of the options is to use  a Command.  A Command is just SQL you write in a Crystal window and it is treated much like a stored procedure.

I think in your case where you are trying to filter the data as you are, it is probably bext to use a stored procedure to get the data you want.

mlmcc
0
 
LVL 35

Expert Comment

by:James0628
ID: 35778632
Are you saying that if a customer has any orders after your date range, you don't want to see their history?  Or, to put it another way, why does it matter if the date range includes their most recent order, as opposed to just any orders?

 If you don't want the history for a customer if they have any orders after the date range, you could theoretically add another EXISTS for that.  Using mlmcc's example, but with Start_date and End_date parameters:

WHERE
CMS.CustNum IN (SELECT C1.CustNum FROM CMS C1 WHERE C1.OrderDate BETWEEN @Start_date AND @End_date) AND
CMS.CustNum NOT IN (SELECT C1.CustNum FROM CMS C1 WHERE C1.OrderDate > @End_date)



 As for using a stored procedure vs a CR Command, theoretically a stored procedure can be more efficient, because the server can optimize a stored procedure.

 James
0
 

Author Closing Comment

by:makingitbig
ID: 35781548
I needed to modify it based upon my specific situation but that was fairly easy.  In the end, I used both a stored procedure and some features within Crystal Reports to achieve the desired results.

Thanks!
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

872 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