Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 386
  • Last Modified:

Crystal XI selection criteria for last record based upon date.

This record selection formula has me stumped. I want to report on active customers that have had no quote activity in the last 6 months from the current month.

CustCode.Active = Y is the easy part

Quote numbers are sequentially assigned regardless of customer... ideally I would like to list the last 3 quotes for each customer.

Two Tables: CustCode and Quote
Join Fields:  CustCode.CustCode = Quote.CustCode and CusCodec.SalesId = Quote.SalesId

Exclude all customer quotes that have Quote.DateEnt or Quote.DateMod within 6 months of the current month.

If the last Quote.QuoteNo is 6 months or older from current month based on Quote.DateEnt or Quote.DateMod

Include the last 3 records consisting of: CustCode.Active,  CustCode.SalesID, CustCode.CustName, Quote.QuoteNo, Quote.DateEnt, Quote.DateMod
0
Lee Ingalls
Asked:
Lee Ingalls
3 Solutions
 
Kent FichtnerInformation Technology Systems SupervisorCommented:
This should stop anything that is 6 months or newer.

DateDiff(M,Quote.DateEnt,CurrentDate) <= 6 OR DateDiff(M,Quote.DateMod ,CurrentDate) <= 6;

as for printing out the three records you will probably have to do a formula loop.  

Formula reset in the group header

formula to count to three in the details, then have a suppression on the details that checks if the formula is equal to three.
0
 
mlmccCommented:
That doesn't work because you need to also check if they had a quote in the last 6 months.

What database are you using?

You will have to do this with a stored procedure or view in the database or you can use a Crystal command assuming your data source suports them.

Here is a start on the command

SELECT CustCode.Active,  CustCode.SalesID, CustCode.CustName, Quote.QuoteNo, Quote.DateEnt, Quote.DateMod 
FROM CustCode LEFT OUTER JOIN Quote ON  CustCode.CustID = Quote.CustId
WHERE
CustCode.Active = Y
AND
Not (CustCode.CustName IN (SELECT CustCode.CustName FROM CustCode INNER JOIN Quote ON  CustCode.CustID = Quote.CustId 
AND ((Quote.DateEnt >= CurrentDate - 182) OR (Quote.DateMod >= CurrentDate - 182))))

Open in new window

0
 
Lee IngallsDirector of IT/TS, Quality and FinanceAuthor Commented:
The database is SQL Server 2008.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
James0628Commented:
FWIW, I think this could be done with a report that was just reading the tables, but it would probably be much more efficient to use a special query, as mlmcc suggested.  A report reading the tables directly would probably have to read all of the quotes, going back to the beginning of time, unless there's some rule that could narrow things down, like active customers will always have a quote within the past 2 years.

 FWIW2, I think mlmcc's query could be simplified a bit.  I don't think you need the CustCode table in the sub-query.  You could just read Quote and produce a list of Quote.CustCode values for the quotes that meet your date criteria.

 However, there may be a complication.  If you need both Quote.CustCode and Quote.SalesId to accurately connect the Quote and CustCode tables, then you may need to change the query.  Off the top of my head, I'm not sure how best to handle that.  Two sub-queries, one for Quote.CustCode and one for Quote.SalesId seems inefficient (assuming that it would even work).

 James
0
 
Lee IngallsDirector of IT/TS, Quality and FinanceAuthor Commented:
Working  on a query based on mlmcc's stored procedure suggestion with the input from James0628 as well. Hope to have it resolved today.
0
 
Lee IngallsDirector of IT/TS, Quality and FinanceAuthor Commented:
Using a view and directly from the report were both successful. I re-purposed mlmcc's command and used james0628's suggestion of narrowing the scope of records and NOT needing both the CustCode and the SalesID... CustCode was sufficient. You guys rock!!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now