Solved

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

Posted on 2011-05-10
252 Views
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
Question by:makingitbig

LVL 40

Expert Comment

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
``````
0

LVL 100

Expert Comment

Are you trying to do this in the report or in SQL?

mlmcc
0

Author Comment

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

Author Comment

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
``````

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

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 100

Expert Comment

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 40

Expert Comment

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

Author Comment

The tables are:

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

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

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

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 100

Expert Comment

I can delete it and you can add it back as a text file

mlmcc
0

Author Comment

will do; thanks.
0

LVL 40

Expert Comment

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
*/
``````
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

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

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

LVL 100

Expert Comment

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 40

Expert Comment

I need some sample data from two tables with expected result.
0

LVL 100

Expert Comment

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 34

Expert Comment

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

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 100

Accepted Solution

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 34

Expert Comment

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

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 100

Expert Comment

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 34

Expert Comment

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

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

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 …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.