Solved

Crystal XI selection criteria for last record based upon date.

Posted on 2014-03-19
6
380 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 100

Assisted Solution

by:mlmcc
mlmcc earned 200 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 8

Author Comment

by:Lee Ingalls
ID: 39941418
The database is SQL Server 2008.
0
Independent Software Vendors: 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 300 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 8

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 8

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

INTRODUCTION The purpose of this document is to demonstrate the Installation and configuration of the Data Protection Manager product. Note that this demonstration was prepared on the basis of Windows OS is 2008 R2 and DPM 2010. DATA PROTECTI…
When you upgrade from Windows 8 to 8.1 or to Windows 10 or if you are like me you are on the Insider Program you may find yourself with many 450MB recovery partitions.  With a traditional disk that may not be a problem but with relatively smaller SS…
In this video, we discuss why the need for additional vertical screen space has become more important in recent years, namely, due to the transition in the marketplace of 4x3 computer screens to 16x9 and 16x10 screens (so-called widescreen format). …
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…

735 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