Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Crystal XI selection criteria for last record based upon date.

Posted on 2014-03-19
6
Medium Priority
?
384 Views
Last Modified: 2014-04-09
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
Comment
Question by:Lee Ingalls
6 Comments
 
LVL 4

Expert Comment

by:Kent Fichtner
ID: 39940780
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
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 800 total points
ID: 39941229
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
 
LVL 9

Author Comment

by:Lee Ingalls
ID: 39941418
The database is SQL Server 2008.
0
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!

 
LVL 35

Accepted Solution

by:
James0628 earned 1200 total points
ID: 39941855
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
 
LVL 9

Assisted Solution

by:Lee Ingalls
Lee Ingalls earned 0 total points
ID: 39950465
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
 
LVL 9

Author Closing Comment

by:Lee Ingalls
ID: 39988229
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Log files are useful in diagnosing and repairing problems.  This is a list of common log files and their standard locations that I've compiled.   While this is not exhaustive, it is a pretty good list that I've found to be useful.  I may update it f…
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

885 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