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

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

Select allOpen in new window

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

mlmcc

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.

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

Select allOpen 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!

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.

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

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

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?  

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

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?)

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

mlmcc

will do; thanks.

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

Select allOpen 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.

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

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

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

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

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

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

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!

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

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?

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

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

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!